Formula to add two spaces into a number stored as text

[mic...@riversway.net]

Hello. I'm trying to do as the title says. The numbers must be arranged "0000 000 0000" and I'm trying to get that format applied to all the numbers in a column. The only problem is there are about 3000 numbers in that column so I can't do it manually.

From searching around I've found the following formula =CONCATENATE(LEFT(H2,4)," ",RIGHT(H2,3))

This seems to be what I'm looking for but I'm having trouble adapting it for my purposes. Anyone who can help me out here?

[Claus Busch]

Hi Micheal,

Am Fri, 17 May 2013 04:20:55 -0700 (PDT) schrieb mic...@riversway.net:

> Hello. I'm trying to do as the title says. The numbers must be arranged "0000 000 0000" and I'm trying to get that format applied to all the numbers in a column. The only problem is there are about 3000 numbers in that column so I can't do it manually.

try custom numberformat:
0000 000 0000

Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

[mic...@riversway.net]

I have tried that already, doesn't seem to do anything.

[Claus Busch]

Hi Michael,

Am Fri, 17 May 2013 04:25:06 -0700 (PDT) schrieb mic...@riversway.net:

> I have tried that already, doesn't seem to do anything.

click in column header => Format "General" => TextToColumns => Fixed
Width => Finish => Custom numberformat 0000 000 0000

If that not works try:
=LEFT(A1,4)&" "&MID(A1,5,3)&" "&RIGHT(A1,4)

[Ron Rosenfeld]

Number formatting of the cell won't work as the values, you write, are text.
The Data/ Text-to-columns wizard should convert these to numbers as Hans wrote.

If it doesn't, there are probably some hidden, non-numeric characters in the value. The most common, especially if the data comes from the Web or an HTML document, is the no-break backspace (nbsp)
You can remove this with

=substitute(h2,char(160),"")
And then work on those values.