Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Eliminate digits in a product identification number
My current product number is 'ABC 0000030000030000--yes that is a label
identifier at the beginning of the id. I would like to eliminate the ABC, the leading zeros, and the four zeros at the end. However, this is the first number of thousands in the column--all of which change in those middle numbers that I need to retain. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Eliminate digits in a product identification number
I have a feeling that there will be surprises if I submit a formula to do this. Are the
first three characters present in all the entries? Should only ABC be removed, or the first three characters in all entries? Are there always four trailing zeroes, or does that vary? Are the last four characters always zeroes, or could they be something else, something that should not be removed? Have I forgotten anything. -- Earl Kiosterud www.smokeylake.com ----------------------------------------------------------------------- "instructorjml" wrote in message ... My current product number is 'ABC 0000030000030000--yes that is a label identifier at the beginning of the id. I would like to eliminate the ABC, the leading zeros, and the four zeros at the end. However, this is the first number of thousands in the column--all of which change in those middle numbers that I need to retain. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Eliminate digits in a product identification number
Assuming all of your data follows the same pattern as your example, place
this formula in an adjacent column. =MID(A1,10,7) Copy down as far as needed. If you then want to delete the original data, follow these steps: Copy your new column of data While that column is still selected, choose "Paste Special" from the Edit Menu Check the "Values" option Click OK Now, delete your original column HTH, Elkar "instructorjml" wrote: My current product number is 'ABC 0000030000030000--yes that is a label identifier at the beginning of the id. I would like to eliminate the ABC, the leading zeros, and the four zeros at the end. However, this is the first number of thousands in the column--all of which change in those middle numbers that I need to retain. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Eliminate digits in a product identification number
Assuming the product number is in A1, then in any cell on same row
=MID(A1,10,7) you may have to play with the 10 and 7 to pull out the exact group you want. 10 is the starting character number and the 7 is how many characters to grab. Note that that will leave whatever is displayed as text and not a real number, if you want a real number (which could lose some leading zeros) then =VALUE(MID(A1,10,7)) If you need to do away with the originals, but keep these results, once you've gotten them all (fill the formulas down the sheet), then select the new values and use Edit | Copy and then without changing anything, choose Edit | Paste Special and choose the [Values] option. The formulas will disappear, to be replaced by the actual values created. Then you could do away with column A with the 'ABC... entries completely if needed/desired. "instructorjml" wrote: My current product number is 'ABC 0000030000030000--yes that is a label identifier at the beginning of the id. I would like to eliminate the ABC, the leading zeros, and the four zeros at the end. However, this is the first number of thousands in the column--all of which change in those middle numbers that I need to retain. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Eliminate digits in a product identification number
If ALL product numbers have the same structu
3-letters, a space, several zeros, the core value, and 4 ending zeros.... try something like this: Select the list of ProdNums From the Excel main menu: <data<text-to-columns Check: Fixed Width.........click [Next] Click to insert a break before the 1st zero Click to insert a break before the last 4 zero s Click [Next] Select the first text column and Check: do not import column Select the 2nd text column and Check: General format Select the last text column and Check: do not import column .........click [Finish] That will leave the core number. If formatted as General....the leading zeros will be removed. Is that something you can work with? *********** Regards, Ron XL2002, WinXP "instructorjml" wrote: My current product number is 'ABC 0000030000030000--yes that is a label identifier at the beginning of the id. I would like to eliminate the ABC, the leading zeros, and the four zeros at the end. However, this is the first number of thousands in the column--all of which change in those middle numbers that I need to retain. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Eliminate digits in a product identification number
Thank you so much. You have made a seamingly difficult task much, much
easier. I really need to explore those functions that I never use. J. LaRose "Elkar" wrote: Assuming all of your data follows the same pattern as your example, place this formula in an adjacent column. =MID(A1,10,7) Copy down as far as needed. If you then want to delete the original data, follow these steps: Copy your new column of data While that column is still selected, choose "Paste Special" from the Edit Menu Check the "Values" option Click OK Now, delete your original column HTH, Elkar "instructorjml" wrote: My current product number is 'ABC 0000030000030000--yes that is a label identifier at the beginning of the id. I would like to eliminate the ABC, the leading zeros, and the four zeros at the end. However, this is the first number of thousands in the column--all of which change in those middle numbers that I need to retain. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to return first 3 digits of a number? | Excel Worksheet Functions | |||
adding digits of a number | Excel Worksheet Functions | |||
How Do I Eliminate the Serial Number for time from Date? | Excel Discussion (Misc queries) | |||
How do I find the sum of all digits in a number? | Excel Discussion (Misc queries) | |||
help with removing digits from a number | Excel Discussion (Misc queries) |