Adding A Space Into Excel Cells

Written by

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.

Comments