S
sharonkturner
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?
leading zero. Is there a simple way to make the correction?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
D
Duke Carey
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
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
F
Fredrik Wahlgren
sharonkturner said: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
S
sharonkturner
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
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
S
sharonkturner
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
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
F
Fredrik Wahlgren
sharonkturner said: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
R
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
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