Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to replace blank space with nothing/underscore/dash/comma in Excel?

This article is talking about replacing all blank spaces with underscore/dash/comma or nothing from selection, multiple worksheets, or multiple workbooks easily in Excel.

Replace blank space with nothing/underscore/dash/comma with Find and Replace feature

Replace blank space with nothing/underscore/dash/comma in multiple worksheets/workbooks

Easily remove leading/trailing/extra spaces in cells

Kutools for Excel’s Remove Spaces utility enables Excel users to easily remove all leading space, trailing space, extra spaces, or all spaces from selected cells quickly. Full Feature Free Trial 60-day!

ad remove space 1



This method will apply the Find and Replace feature to replace blank spaces with nothing or underscore/dash/comma from selected cells easily.

1. Select the range you will replace the blank spaces, and press the Ctrl + H keys at the same time to open the Find and Replace dialog box.

Note: You can also open the Find and Replace dialog box by clicking Home > Find & Select > Replace.

2. In the opening Find and Replace dialog box, please do as follows:

(1) In the Find what box, please type a space;

(2) In the Replace with box, type underscore/dash/comma as you need. (To replace blank space with nothing, please keep this box blank);

(3) Click the Replace All button.

3. And then a dialog box pops up and shows the total number of replacements it has made. Just Click the OK button to close it. And then close the Find and Replace dialog box.

And then you will see all blank spaces are replaced with the specified comma/dash/underscore or nothing based on your configuration. See screenshot:

doc replace blank space with underscore 3


For example you have many worksheets with similar data from different workbooks, and you need to batch replace all blank spaces with nothing/underscore/dash/comma from these workbooks.

Apart from replacing in each workbook manually, Kutools for Excel’s Advanced Find and Replace utility can help you batch replace in multiple worksheets and workbooks easily.

Kutools for Excel - Combines more than 300 Advanced Functions and Tools for Microsoft Excel

1. Open all workbooks you will make replacements in, and click Kutools > Navigation Pane > Advanced Find and Replace button doc replace blank space with underscore 5 to activate the Advanced Find and Replace pane. See screenshot:

2. In the Advanced Find and Replace pane, go to the Replace tab, and:

(1) In the Find what box, please type a space;

(2) In the Replace with box, type underscore/dash/comma as you need. (To replace blank space with nothing, please keep this box blank);

(3) Specify the replacing scope from the Within drop down list;

Note: If you select Selected Sheets from the Within drop down list, all worksheets of opening workbooks are listing in the Workbooks box. And then you need to check the worksheets in the Workbooks box.

3. Click the Replace All button.

And then all blank spaces are replaced with nothing or specified underscore/dash/comma from the specified worksheets and workbook at once.

Kutools for Excel - Includes more than 300 handy Excel tools. Full feature free trial 60-day, no credit card required! Get it now!


In this Video, the Kutools tab and the Kutools Plus tab are added by Kutools for Excel. If need it, please click here to have a 60-day free trial without limitation!



Recommended Productivity Tools

Ribbon of Excel (with Kutools for Excel installed)

300+ Advanced Features Increase Your Productivity by 71%, and Help You To Stand Out From Crowd!

Would you like to complete your daily work quickly and perfectly? Kutools For Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for 1500+ work scenarios, helps you solve 82% Excel problems.

  •  Deal with all complicated tasks in seconds, help to enhance your work ability, get success from the fierce competition, and never worry about being fired.
  •  Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
  •  Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands, and give you a healthy body.
  •  Become an Excel expert in 3 minutes, and get admiring glance from your colleagues or friends.
  •  No longer need to remember any painful formulas and VBA codes, have a relaxing and pleasant mind, give you a thrill you've never had before.
  •  Spend only $39, but worth than $4000 training of others. Being used by 110,000 elites and 300+ well-known companies.
  •  60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
  •  Change the way you work now, and give you a better life immediately!

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Open and create documents in new tabs of same window, rather than in new windows.
  • Help you work faster and easily stand out from the crowd! One second to switch between dozens of open documents!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.