Want to split 922 037 022 into 3cols and recombine without spaces
 

I have some employee numbers in the format 923 022 033. I want to put the
numbers together without spaces. I split into cols and then recombined but
the leading zeros were missing. So instead of getting 922037022 I get
9232233. Any solution

Want to split 922 037 022 into 3cols and recombine without spaces
 

Try

=SUBSTITUTE(A1," ","")

Mike

"Confused" wrote:

I have some employee numbers in the format 923 022 033. I want to put the
numbers together without spaces. I split into cols and then recombined but
the leading zeros were missing. So instead of getting 922037022 I get
9232233. Any solution

Want to split 922 037 022 into 3cols and recombine without spaces
 

With originals try editreplace to replace the spaces with nothing.

If you do want to split first then recombine use datatext to
columnsdelimited by spacenextnextselect all three columns and column
Data formattextfinish


Gord Dibben MS Excel MVP

On Thu, 25 Jun 2009 12:59:01 -0700, Confused
wrote:

I have some employee numbers in the format 923 022 033. I want to put the
numbers together without spaces. I split into cols and then recombined but
the leading zeros were missing. So instead of getting 922037022 I get
9232233. Any solution

Want to split 922 037 022 into 3cols and recombine without spaces
 

If you split the number into 3 cells then you either had to format the cells
as TEXT to keep the leading 0s, or, you used a custom number format of 000,
in which case the leading 0s are for display purposes only.

If the cells are TEXT then a formula like =A1&B1&C1 will retain the leading
0s.

If the cells use a custom number format of 000, then try this formula:

=TEXT(A1,"000")&TEXT(B1,"000")&TEXT(C1,"000")

--
Biff
Microsoft Excel MVP


"Confused" wrote in message
...
I have some employee numbers in the format 923 022 033. I want to put the
numbers together without spaces. I split into cols and then recombined but
the leading zeros were missing. So instead of getting 922037022 I get
9232233. Any solution

Want to split 922 037 022 into 3cols and recombine without spaces
 

Try
=SUBSTITUTE(A1," ",)

If this post helps click Yes
---------------
Jacob Skaria


"Confused" wrote:

I have some employee numbers in the format 923 022 033. I want to put the
numbers together without spaces. I split into cols and then recombined but
the leading zeros were missing. So instead of getting 922037022 I get
9232233. Any solution