How do I remove leading zero in large DB?

[Guest]

I have a rather large (thousands) db of part numbers that have an extra
leading zero. Is there a simple way to make the correction?

 

[Guest]

Well, do you want to keep the part numbers as text, or do you want to convert
them to numbers?

If text, insert a blank column next to the part numbers and (assuming the
part numbers are in column B), use this formula to strip off the first
character of the cell

=RIGHT(B1,LEN(B1)-1)

if you want the part numbers as numbers,
1) format the column as numbers
2) then enter a '1' in an empty cell and copy it
3) select the part numbers and from the menu choose Edit | Paste Special |
Multiply | Values

 

[Fredrik Wahlgren]

I have a rather large (thousands) db of part numbers that have an extra
leading zero. Is there a simple way to make the correction?

Assuming that your part number is in column A, you can use a formula like
this in another column, say D
=RIGHT(A1,LEN(A1)-1)

However, if not all part numbers have a leading zero, use this instead

=IF("0" =LEFT(A1,1),RIGHT(A1,LEN(A1)-1),A1)

Drag the function downwards. Having don that, select all the values in
Column D and then copy them. Now select the A column and then do a Paste
Special... Select the "Values" radio button and hit OK. I have the Swedish
version of Excel, I hope my translation is OK. As always, use a copy of your
original workbook when you do a major change.

Best Regards,
Fredrik

 

[Guest]

Actually, my part numbers all have one (1) leading zero.
Example: 091-0002, 098-1003
There are three (3) places, a hyphen (-) and from four (4) places and
sometimes another hyphen (-) and two to three more numbers
Example: 098-1812-01
The way the file was sent, there were too many leading zeros.
Example: 0091-0002, 0098-1003. etc.
There are over 10,000 entries--too many to deal with individually. I need a
quick and easy way to make the corrections.
Sharon

 

[Guest]

Actually, my part numbers all have one (1) leading zero.
Example: 091-0002, 098-1003
There are three (3) places, a hyphen (-) and from four (4) places and
sometimes another hyphen (-) and two to three more numbers
Example: 098-1812-01
The way the file was sent, there were too many leading zeros.
Example: 0091-0002, 0098-1003. etc.
There are over 10,000 entries--too many to deal with individually. I need a
quick and easy way to make the corrections. Will this take care of that
problem?
Sharon

 

[Fredrik Wahlgren]

Actually, my part numbers all have one (1) leading zero.
Example: 091-0002, 098-1003
There are three (3) places, a hyphen (-) and from four (4) places and
sometimes another hyphen (-) and two to three more numbers
Example: 098-1812-01
The way the file was sent, there were too many leading zeros.
Example: 0091-0002, 0098-1003. etc.
There are over 10,000 entries--too many to deal with individually. I need a
quick and easy way to make the corrections.
Sharon

Since you only have to drag the formula and then do a copy and paste..
shouldn't take more than 2 minutes to do this. I don't think there's any
faster or easier approach.

/Fredrik

 

[RagDyer]

Here is a copy of a suggestion I posted a few hours ago to answer a similar
request:

<<"Select the column with the part numbers, then:

<Data> <Text To Columns>
Check "Fixed Width", then,
<Next>
Click on, and drag the break line to separate the data as you wish, then,
<Next>

You can now decide on several options:

1> To keep the original column of data, and create 2 new columns of
*separated* data, click in the "Destination" window and enter the address of
the column to start receiving the separated data (ie, G1), then click
<Finish>.

2> To separate the data into 2 separate columns, starting in the original
column, click <Finish>

3> To "throw away" the 3 letter code, and keep the part number *only*, in
the original column, make sure the code section is selected (black), and
click in "Do Not Import Column".
You'll see the header change from "General" to *Skip*.
Then click <Finish>

There are a few more options which I'm sure you can figure out from just
these examples.">>

--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


Actually, my part numbers all have one (1) leading zero.
Example: 091-0002, 098-1003
There are three (3) places, a hyphen (-) and from four (4) places and
sometimes another hyphen (-) and two to three more numbers
Example: 098-1812-01
The way the file was sent, there were too many leading zeros.
Example: 0091-0002, 0098-1003. etc.
There are over 10,000 entries--too many to deal with individually. I need a
quick and easy way to make the corrections.
Sharon