How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask Zabo04 Your Own Question

Zabo04
Zabo04, Computer Enthusiast
Category: Microsoft Office
Satisfied Customers: 282
Experience:  Experienced in Word, Excel, Access, Powerpoint, and Outlook.
58597962
Type Your Microsoft Office Question Here...
Zabo04 is online now
A new question is answered every 9 seconds

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: 4 years ago.
Category: Microsoft Office
Expert:  Zabo04 replied 4 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:

Zabo04 and 2 other Microsoft Office Specialists are ready to help you