Eliminate digits in a product identification number

[Guest]

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.

 

[Earl Kiosterud]

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.

 

[Guest]

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

 

[Guest]

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.

 

[Guest]

If ALL product numbers have the same structure:
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

 

[Guest]

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