MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Show listing with numbers only


Posted by A.R. on October 26, 2001 7:02 AM

I have a listing in one column such as “box#5, box#12, etc. I need to change them to numbers only such as “5”, “12”, etc. Is there a quick method that will change them to numbers only (getting rid of “box”) all at once? I have over 50 rows and doing it manually will be too time consuming.


Posted by Barrie Davidson on October 26, 2001 7:11 AM

Use this formula (assumes data in column A) and then Copy|PasteValues to get the numbers only.

=VALUE(RIGHT(A1,LEN(A1)-FIND("#",A1)))

Regards,
BarrieBarrie Davidson

Posted by Mark W. on October 26, 2001 7:14 AM

Using Edit | Replace... you could replace "box#" with "" (nt)

Posted by Don C on October 26, 2001 7:15 AM

Just highlight the column and replace "Box#" with nothing (in the ctrl/cmd-H dialog box pt Box# in the first box and leave the replace box empty.