Remove Leading Space from Multiple Rows (Excel 2013)

My latest adventure in work got me splitting some data into rows and then scratching my head (unnecessarily) for a situation I didn’t expect after the split… There are over 40 country codes living happily together in one cell but I have to be Cruella de Vil and split them up into lonely individual row. Unfortunately, the split comes with excess baggage – a leading space.

All countries in one cell

The first step is super easy to do. Just use the Text to Columns function to split them into columns and then copy and paste the results into rows. It is the second step that requires a little extra effort. After the split, the country code from the second row onwards has a leading space to it. I can, of course, go into each cell and remove the space by deleting it one by one. But by the time I am done, the cows would have gone home already!

That darn extra space…

The easiest way to get rid of this extra space is by using the Find and Replace function!


The Find and Replace function has many useful tricks – not just finding something and replacing that something with something else. Here’s how to find that extra space and replace it with NOTHING:

1.  Press the Ctrl and H buttons on your keyboard at the same time.

2.  A Find and Replace dialog box appears. In the Find what field, press the space bar once. Leave the Replace with field blank as it is. Then, click the Replace All button.

3.  Mama mia! 57 countries? Click the OK button and see the changes yourself!

Be gone, all you extras, good for nothing…err…language, language…

Oh hey, guess what? You can also use the Find and Replace function to find a new boss and replace your existing one.



Leave a Reply

Your email address will not be published. Required fields are marked *