ExcelBanter - View Single Post - Formula to add two spaces into a number stored as text

Ron Rosenfeld[_2_]

On Fri, 17 May 2013 04:20:55 -0700 (PDT), wrote:

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?

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.