MrExcel Publishing
Your One Stop for Excel Tips & Solutions

delet the last two digits in a string of serial # in a column


Posted by Allan on February 13, 2001 6:12 AM

I have a column with hundreds of rows of serial # like this: 37A800101
37A703030
37A494094
37A303030
I want to delete the last two digits of each serial #.I have tried to use the wildcard"*" and "?" in replace function in different ways, like replace: 37A?????? with 37A????. But none of them work. The function replace all cells with 37A???? in stead of automatically pick up the next 4 digits accordingly.

Please help me with this matter ASAP .
thanks in advance.


Posted by Loren on February 13, 2001 6:34 AM


Since all your #s appear to be 9 digits, use =Left function,
in a new column.

Posted by Mark W. on February 13, 2001 7:32 AM

Allan, if you'd prefer not to deal with a new
column consider using the Data Text to Columns...
menu command. At Step 1 of 3 choose "Fixed width"
and press the Next> button. At Step 2 of 3 click
between the 7th and 8th columns and press the
Next> button. At Step 3 of 3 select the column
containing the last 2 characters, assign a "Do
not import column (skip)" format, and press the
Finish button.

This may seem overly complex, but believe me it
takes longer to read these instructions than it
does to do the task.