• 100% Satisfaction Guarantee
Zabo04, Computer Enthusiast
Category: Microsoft Office
Satisfied Customers: 283
Experience:  Experienced in Word, Excel, Access, Powerpoint, and Outlook.
58597962
Type Your Microsoft Office Question Here...
Zabo04 is online now

# Excel - filtering text out of cell with numbers

### Resolved Question:

I need to sort two worksheets by Dewey Decimal number, but ignoring the prefixes. For example, E398.2, J398.2, 001.24, Y891.7, etc. Is there a way to separate the text prefixes into their own column, so that the numbers can be sorted?

 What I have What I Need 006.686 KEL J 001.420285 GAI J001.420285 GAI E 001.94 BEL E002 DON J 001.942 HER J001.942 HER E 002 DON Y006.3023 GRE J 004.678028 JAK J004.678028 JAK J 005.8 JAK J005.8 JAK Y 006.3023 GRE E001.94 BEL 006.686 BIN 006.686 BIN 006.686 KEL
Submitted: 5 years ago.
Category: Microsoft Office
Expert:  Zabo04 replied 5 years ago.

Zabo04 :

You could just find the numbers within the spreadsheet using this formula, where A1 is the cell containing the dewey decimal number: =RIGHT(A2,LEN(A2)-MIN(IFERROR(FIND(0,A2),L EN(A2)),IFERROR(FIND(1,A2),LEN(A2)),IFERROR(FIND(2,A2),LEN(A2)),IFERROR(FIND(3,A2),LEN(A2)),IFERROR(FIND(4,A2),LEN(A2)),IFERROR(FIND(5,A2),LEN(A2)),IFERROR(FIND(6,A2),LEN(A2)),IFERROR(FIND(7,A2),LEN(A2)),IFERROR(FIND(8,A2),LEN(A2)),IFERROR(FIND(9,A2),LEN(A2)))+1)

Zabo04 :

If you want the leading prefix use this formula, where A2 is again the cell containing the dewey decimal number: =TRIM(LEFT(A2,MIN(IFERROR(FIND(0,A2),LEN(A2)),IFERROR(FIND(1,A2),LEN(A2)),IFERROR(FIND(2,A2),LEN(A2)),IFERROR(FIND(3,A2),LEN(A2)),IFERROR(FIND(4,A 2),LEN(A2)),IFERROR(FIND(5,A2),LEN(A2)),IFERROR(FIND(6,A2),LEN(A2)),IFERROR(FIND(7,A2),LEN(A2)),IFERROR(FIND(8,A2),LEN(A2)),IFERROR(FIND(9,A2),LEN(A2)))-1))

Zabo04 :

If you attach the spreadsheet and add the two columns (with headers) so that I can see the headers for how the spreadsheet should look, and fill in the first two rows (books), I'll setup the spreadsheet for you.

Zabo04 :

What I meant by that is I need the empty columns for where the prefix and numbers go, and two examples, I don't need you to do every entry, otherwise there wouldn't be a point to you asking your question, you would have done that in the first place.

Customer:

Here's a small portion of the spreadsheet: