G
Guest
I am doing a text to columns function using fixed width. Once this is done,
it drops any preceding zeros. Any ideas how to stop dropping the zeros?
it drops any preceding zeros. Any ideas how to stop dropping the zeros?
D
David Biddulph
Towards the end of the Text to Columns, specify the format of the relevant
columns as text.
columns as text.
R
RagDyeR
Are you using the results as numbers, or text?
If text, in the 3rd step of the wizard, select the column with these values,
and then click on "Text" under <Column Data Format>.
--
HTH,
RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================
I am doing a text to columns function using fixed width. Once this is done,
it drops any preceding zeros. Any ideas how to stop dropping the zeros?
If text, in the 3rd step of the wizard, select the column with these values,
and then click on "Text" under <Column Data Format>.
--
HTH,
RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================
I am doing a text to columns function using fixed width. Once this is done,
it drops any preceding zeros. Any ideas how to stop dropping the zeros?
G
Guest
I have tried selecting text in the 3rd step of the text to columns but still
drops the preceding zeros. No one at my office is able to help.
drops the preceding zeros. No one at my office is able to help.
P
Pete_UK
You must highlight each column in turn (by clicking on it) and then
select Text. Do this for each column that you want to treat this way.
Hope this helps.
Pete
select Text. Do this for each column that you want to treat this way.
Hope this helps.
Pete
G
Guest
I have selected the entire column..changed format to text and then do a text
to column (delimited) and selected text on the last step.
It still drops any preceding zeros...must be a magic answer somewhere?!
to column (delimited) and selected text on the last step.
It still drops any preceding zeros...must be a magic answer somewhere?!
S
Susan
well, i don't have a magic answer but i have an idea 
1. do all the #s have the SAME NUMBER of leading zeros???
2. tell me the range that holds the numbers (like a5:f25).
i'll write you a quickie macro that will run thru the range & add
'000
or how many ever zeros, to every # in the list.......
IF they all use the same # of zeros, or IF you can tell me some kind
of delimiting information (like all the numbers with the letter C in
them get 3 leading zeros, all the others get 4) (or numbers with 3
characters get 4 zeros, numbers with 4 characters get 3 zeros, numbers
with 5 characters get 2 zeros, etc.)
susan
1. do all the #s have the SAME NUMBER of leading zeros???
2. tell me the range that holds the numbers (like a5:f25).
i'll write you a quickie macro that will run thru the range & add
'000
or how many ever zeros, to every # in the list.......
IF they all use the same # of zeros, or IF you can tell me some kind
of delimiting information (like all the numbers with the letter C in
them get 3 leading zeros, all the others get 4) (or numbers with 3
characters get 4 zeros, numbers with 4 characters get 3 zeros, numbers
with 5 characters get 2 zeros, etc.)
susan
D
David Biddulph
Are you sure you are seeing "Text" in the header of EACH of the output
columns in that final stage of the Text to Columns wizard before you hit the
Finish button? It works for me. Which version of Excel are you using?
columns in that final stage of the Text to Columns wizard before you hit the
Finish button? It works for me. Which version of Excel are you using?
S
Susan
i had some time so i went ahead & did this. it works for me, making
all the #s 5 digits by adding leading zeros. it's a worksheet
button. you could easily change it to run on whatever range you have
selected, instead of specifying a specific range.
susan
=================
Option Explicit
Private Sub CommandButton1_Click()
Dim ws As Worksheet
Dim rRange As Range
Dim c As Range
Dim sZeros As String
Dim sNumber As String
Dim myCell As String
'the objective is to make them all 5 digits
'with the proper amount of leading zeros
Set ws = ActiveSheet
Set rRange = ws.Range("b2:b12")
On Error Resume Next
For Each c In rRange
myCell = c
sNumber = c.Value
If Len(myCell) = 1 Then
sNumber = "'0000"
ElseIf Len(myCell) = 2 Then
sNumber = "'000"
ElseIf Len(myCell) = 3 Then
sNumber = "'00"
ElseIf Len(myCell) = 4 Then
sNumber = "'0"
ElseIf Len(myCell) = 5 Then
'do nothing
End If
If Len(myCell) <> 5 Then
c = sNumber + myCell
myCell = ActiveCell.Offset(1, 0)
Else
sNumber = "'"
c = sNumber + myCell
myCell = ActiveCell.Offset(1, 0)
End If
Next c
End Sub
=========================
all the #s 5 digits by adding leading zeros. it's a worksheet
button. you could easily change it to run on whatever range you have
selected, instead of specifying a specific range.
susan
=================
Option Explicit
Private Sub CommandButton1_Click()
Dim ws As Worksheet
Dim rRange As Range
Dim c As Range
Dim sZeros As String
Dim sNumber As String
Dim myCell As String
'the objective is to make them all 5 digits
'with the proper amount of leading zeros
Set ws = ActiveSheet
Set rRange = ws.Range("b2:b12")
On Error Resume Next
For Each c In rRange
myCell = c
sNumber = c.Value
If Len(myCell) = 1 Then
sNumber = "'0000"
ElseIf Len(myCell) = 2 Then
sNumber = "'000"
ElseIf Len(myCell) = 3 Then
sNumber = "'00"
ElseIf Len(myCell) = 4 Then
sNumber = "'0"
ElseIf Len(myCell) = 5 Then
'do nothing
End If
If Len(myCell) <> 5 Then
c = sNumber + myCell
myCell = ActiveCell.Offset(1, 0)
Else
sNumber = "'"
c = sNumber + myCell
myCell = ActiveCell.Offset(1, 0)
End If
Next c
End Sub
=========================