Excel cell formatting for text

Conan257 · 8 Aug 2013 at 09:25

I'm creating a spreadsheet but need some boxes to have spaces in the text. Ideally I'd like this to be an automated function.

ie.

Operator enters: abcdefghi
Cell displays: abc def ghi

Any ideas?

Lartyconshayo · 8 Aug 2013 at 10:07

Is it possible to count the string and then add a space every three characters ? On a mobile here so can't tinker but either that or the left right middle functions will have some bearing on what you want to do. Will it be spacing every three characters ?

Conan257 · 8 Aug 2013 at 10:27

Yes, every entry will be 9 letter long, and I want to add a space between 3+4 and 6+7 so it appears as above...

Conan257 · 8 Aug 2013 at 10:29

I can do it with numbers easy enough....

###_####_#### gives me the right formatting with numbers, I just can't do it with letters!

Daveangle · 8 Aug 2013 at 14:18

As mentioned before you can do this with LEFT, MID and RIGHT

Cell A1 Contains: ABCDEFGHI

=CONCATENATE(LEFT(A1,3)," ",MID(A1,4,3)," ",RIGHT(A1,3))

Output ABC DEF GHI

To do this automatically though you would need to implement some cell watching in VBA i'd imagine to change the content to use the above formula based on whatever the cell was.