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?

Towards the end of the Text to Columns, specify the format of the relevant
columns as text.
--
David Biddulph

"Rene''" wrote in message
...
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?

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!
================================================== ===

"Rene''" wrote in message
...
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?

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.

"RagDyeR" wrote:

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!
================================================== ===

"Rene''" wrote in message
...
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?

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

On Apr 23, 5:32 pm, Rene'''' wrote:
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.



"RagDyeR" wrote:
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!
================================================== ===

"Rene''" wrote in message
...
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?- Hide quoted text -

- Show quoted text -

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?!


"Pete_UK" wrote:

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

On Apr 23, 5:32 pm, Rene'''' wrote:
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.



"RagDyeR" wrote:
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!
================================================== ===

"Rene''" wrote in message
...
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?- Hide quoted text -

- Show quoted text -

well, i don't have a magic answer but i have an idea :D
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


On Apr 23, 12:58 pm, Rene'''' wrote:
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?!

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?
--
David Biddulph

"Rene''''" wrote in message
...
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?!


"Pete_UK" wrote:

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

On Apr 23, 5:32 pm, Rene'''' wrote:
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.



"RagDyeR" wrote:
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!
================================================== ===

"Rene''" wrote in message
...
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?- Hide quoted text -

- Show quoted text -

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
=========================





On Apr 23, 2:21 pm, Susan wrote:
well, i don't have a magic answer but i have an idea :D
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

On Apr 23, 12:58 pm, Rene'''' wrote:



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?!- Hide quoted text -

- Show quoted text -