Adding A Space Into Excel Cells

This took much more digging than I thought to find the answer so here it is:

Say you have a field in Excel with a value such as “123456”, but want to display it with a space in the middle – “123 456″… how do you add the space?

You can just add it in manually if the cell type allows it, but for a bunch of data, that’s a very time consuming solution.

There’s probably a bunch of ways it can be achieved, but here’s the easy way I eventually found:

if A1 contains “123456” put this into A2:

=TEXT(A1,”### ###”)

B2 will read “123 456”!

If you have a leading zero in your fields, it will drop the zero. For that, you’ll have to do this:

if A1 contains “012345” put this into A2:

=Text(A1,”0## ###”)

Pretty simple, the hash passes on each character from the referring field, and you can modify what happens between each passed character.

If you want to clean it up, then copy your results, and paste special > results. That will drop the code, and just have your newly formatted results.

2 thoughts on “Adding A Space Into Excel Cells

  1. Excel (2010) seems to let me put a space in regardless, just by typing it. It doesn’t count the cell as a number in that case though, the cell type is “General”.

    What’s more, you can’t rely on the cell with the specific formatting for arithmetic calculations. :(

    1. Yeah you’re right – I’ll edit this post slightly to show that this is for mass data manipluation. You can just manually put a space in, but for a bunch of records, that isn’t practical. Apologies for not making it clearer!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.