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

[Confused]

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

 

[Mike H]

Try

=SUBSTITUTE(A1," ","")

Mike

 

[Gord Dibben]

With originals try edit>replace to replace the spaces with nothing.

If you do want to split first then recombine use data>text to
columns>delimited by space>next>next>select all three columns and column
Data format>text>finish


Gord Dibben MS Excel MVP

 

[T. Valko]

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")

 

[Jacob Skaria]

Try
=SUBSTITUTE(A1," ",)

If this post helps click Yes