Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How do I remove leading zero in large DB?
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? |
#2
|
|||
|
|||
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 "sharonkturner" wrote: 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? |
#3
|
|||
|
|||
"sharonkturner" wrote in message ... 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 |
#4
|
|||
|
|||
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 "Fredrik Wahlgren" wrote: "sharonkturner" wrote in message ... 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 |
#5
|
|||
|
|||
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 "Duke Carey" wrote: 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 "sharonkturner" wrote: 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? |
#6
|
|||
|
|||
"sharonkturner" wrote in message ... 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 |
#7
|
|||
|
|||
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! ============================================== "sharonkturner" wrote in message ... 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 "Fredrik Wahlgren" wrote: "sharonkturner" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I remove the leading 1 from a nine digit telephone # | Excel Discussion (Misc queries) | |||
Remove Leading Spaces | Excel Discussion (Misc queries) | |||
Remove link fr a column of entries | Excel Discussion (Misc queries) | |||
Remove link fr a column of entries | Excel Discussion (Misc queries) | |||
Remove link fr a column of entries | Excel Discussion (Misc queries) |