| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I remove split a split window? | New Users to Excel | |||
| how to set tab spaces in excel? | Excel Discussion (Misc queries) | |||
| how do I remove leading spaces and leave the remianing spaces w | Excel Worksheet Functions | |||
| Spaces | Excel Worksheet Functions | |||
| spaces not recognized as spaces | Excel Worksheet Functions | |||
Linear Mode
