Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lead Zeros in Text
We want to use Text to Columns to divide a field with four digits into two
columns with two digits. If one of the digits begins with zero, this does not work - (the zero drops from the display). How do we retain the lead zero in the resulting cell? We have tried formatting the workbook as text prior to importing and afterward too, to no avail. FormatNumberCustom00 did nor work either. 'Suggestions? DOUG |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lead Zeros in Text
an alternative would be to instead in a separate column enter =LEFT(A2,2) and
the next column would be =right(A2,2) This woudl retain your 0's "DOUG" wrote: We want to use Text to Columns to divide a field with four digits into two columns with two digits. If one of the digits begins with zero, this does not work - (the zero drops from the display). How do we retain the lead zero in the resulting cell? We have tried formatting the workbook as text prior to importing and afterward too, to no avail. FormatNumberCustom00 did nor work either. 'Suggestions? DOUG |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lead Zeros in Text
hi
when the first text to columns dialog comes up, choose fixed width. on the second dialog, position your seperator on the third dialog, in the upper right corner, choose text. if you leave it at default of general, excel will strip your leading zeros and convert it back to numbers. choosing text keeps it as text. the other groups may be coverted to numbers unless they are mixed data. click finish. regards FSt1 "DOUG" wrote: We want to use Text to Columns to divide a field with four digits into two columns with two digits. If one of the digits begins with zero, this does not work - (the zero drops from the display). How do we retain the lead zero in the resulting cell? We have tried formatting the workbook as text prior to importing and afterward too, to no avail. FormatNumberCustom00 did nor work either. 'Suggestions? DOUG |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lead Zeros in Text
I had tried importing as TEXT, but it did not work this time for some reason.
What did work was the formula: =REPT("0",4-LEN(D1))&D1 Then, I was able to use the formula: = LEFT (D1,2) Thanks, DOUG "FSt1" wrote: hi when the first text to columns dialog comes up, choose fixed width. on the second dialog, position your seperator on the third dialog, in the upper right corner, choose text. if you leave it at default of general, excel will strip your leading zeros and convert it back to numbers. choosing text keeps it as text. the other groups may be coverted to numbers unless they are mixed data. click finish. regards FSt1 "DOUG" wrote: We want to use Text to Columns to divide a field with four digits into two columns with two digits. If one of the digits begins with zero, this does not work - (the zero drops from the display). How do we retain the lead zero in the resulting cell? We have tried formatting the workbook as text prior to importing and afterward too, to no avail. FormatNumberCustom00 did nor work either. 'Suggestions? DOUG |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lead Zeros in Text
Dear Doug
From the 'Convert Text to Columns' Wizard Step 3; hold 'Shift' key and select the two columns displayed under 'Data Preview Area'. Once selected the background will be black and the text will be white. Then on the same window from the group 'Column Data format' select Text and hit Finish If this post helps click Yes --------------- Jacob Skaria "DOUG" wrote: We want to use Text to Columns to divide a field with four digits into two columns with two digits. If one of the digits begins with zero, this does not work - (the zero drops from the display). How do we retain the lead zero in the resulting cell? We have tried formatting the workbook as text prior to importing and afterward too, to no avail. FormatNumberCustom00 did nor work either. 'Suggestions? DOUG |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lead Zeros in Text
Jacob: That worked - (I had not selected ALL of the columns to format as
text). However, in attempting to use the subsequent command "=LEFT(D1,2)", the formula would not return a number. It just displayed the formula in the cell. Do you know why that happened? DOUG "Jacob Skaria" wrote: Dear Doug From the 'Convert Text to Columns' Wizard Step 3; hold 'Shift' key and select the two columns displayed under 'Data Preview Area'. Once selected the background will be black and the text will be white. Then on the same window from the group 'Column Data format' select Text and hit Finish If this post helps click Yes --------------- Jacob Skaria "DOUG" wrote: We want to use Text to Columns to divide a field with four digits into two columns with two digits. If one of the digits begins with zero, this does not work - (the zero drops from the display). How do we retain the lead zero in the resulting cell? We have tried formatting the workbook as text prior to importing and afterward too, to no avail. FormatNumberCustom00 did nor work either. 'Suggestions? DOUG |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lead Zeros in Text
A work around I just discovered was to split the four digits again, using
TEXT TO COLUMNS again, remembering to select both columns as you just suggested. DOUG "DOUG" wrote: Jacob: That worked - (I had not selected ALL of the columns to format as text). However, in attempting to use the subsequent command "=LEFT(D1,2)", the formula would not return a number. It just displayed the formula in the cell. Do you know why that happened? DOUG "Jacob Skaria" wrote: Dear Doug From the 'Convert Text to Columns' Wizard Step 3; hold 'Shift' key and select the two columns displayed under 'Data Preview Area'. Once selected the background will be black and the text will be white. Then on the same window from the group 'Column Data format' select Text and hit Finish If this post helps click Yes --------------- Jacob Skaria "DOUG" wrote: We want to use Text to Columns to divide a field with four digits into two columns with two digits. If one of the digits begins with zero, this does not work - (the zero drops from the display). How do we retain the lead zero in the resulting cell? We have tried formatting the workbook as text prior to importing and afterward too, to no avail. FormatNumberCustom00 did nor work either. 'Suggestions? DOUG |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Keeping lead zeros in excel text format when saving as CSV file | Excel Worksheet Functions | |||
Excel drops my lead zeros: need 0051 not 51 | Excel Discussion (Misc queries) | |||
Keep Lead Zeros | Excel Discussion (Misc queries) | |||
Deleting Lead Zeros | Excel Worksheet Functions | |||
Lead Zeros to Show? | Excel Worksheet Functions |