NUMBERS TO TEXT

  • Thread starter Thread starter Brent
  • Start date Start date
B

Brent

I have a bar code number that is say i have a bunch of barcode nubers that
have a preceeding zero in front (ie 021908503462) and i want to convert
these to a text or character. I have been using text to columns very well,
be except when I have a bar code starting with zero- it lops off of the 0...
how can I fix this?

Thanks
Brent
 
Brent

In third step of T to C.........Column Data Format>Text


Gord Dibben MS Excel MVP
 
One way:

In the Text to columns dialog, 3rd pane, select the column the leading 0
is in and click the Text radio button.
 
Related to this - how do I format a number so that it shows leading zeros
(in fact is this possible) ?

What I want is say,

23 to be entered, but display as 0023
023 to be entered and display as 0023

and so on

Thanks folk,

Rob Lepper
 
Give it a custom format of
0000

Select the cells
Format|cells|number tab|custom category
 
JE- I went to text to columns and did not see any thing in the third pane
that allowed me to slect the column with the leading zero in it...
 
OK, I put

021908503462

in cell A1 formatted as Text (it works similarly with if the value is
prefixed with an apostrophe).

I chose Data/Text to Columns. In the first pane, I selected the Fixed
width radio button and clicked Next. In the second pane, I clicked
between each digit to insert a line with arrows, then clicked Next.

In the third pane, I selected the column with the leading zero (actually
it was already selected). I clicked the Text radio button, then clicked
Finish.

The result was 0 in A1, 2 in B1, ... 2 in L1.

If you don't see a column with your leading zero, I'm wondering if it
really exists, or instead is a product of formatting.
 
JE- Yes I tried your protocol- Problem:

when I start off I have

000056453805
000056453836
000056453867


When I got into "text to columns"
and used fixed (radio)

The data looks like

56453805
56453836
56453867


so, I am sol right out of the gate..

it must be some sort of formating issue..

OK I think I understand what is going on- The raw data had 56453805 etc
Then I did a custom (00000000000#) and I got 000056453805 etc..

How do i get those zeros in the front?????

Thanks
brent
 
How about using a helper column of cells with formulas like:
=text(a1,"000000000000")

Drag it down the column
convert it to values (edit|copy, edit|paste special|values)
and delete the original column.
 
Back
Top