asked on

Removing all letters after a number in a cell

I have a cell that has "SRF-08 Cat Code 6_Earl Morris" or "SRF-08 Cat Code 9 Kirby Balfour Ag Consign" I want to return "Cat Code #" in one cell, "Name" in the next cell, if there is a descripion after the name "Ag Consign" (otherwise blank) in the next cell and "SRF-##" in another can this be done with a function? I need 4 formulas

ASKER CERTIFIED SOLUTION

NBVC

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

nutsch

assuming your data is in cell A2 and that the underscore after your cat code is a fluke.

for SRF-##
=left(a2, 6)
for cat code # (up to two digits)
=trim(mid(a2,17,2))
for name assuming first + last name, no compound names
=LEFT(RIGHT(A2,LEN(A2)-18),IFERROR(FIND(" ",SUBSTITUTE(RIGHT(A2,LEN(A2)-18)," ","|",1)),LEN(A2)))
for description, with the same assumptions
=IF(LEN(A2)-LEN(SUBSTITUTE(A2," ",""))>5,MID(A2,1+FIND("|",SUBSTITUTE(A2," ","|",6)),LEN(A2)),"")

Thomas

Jenedge73

ASKER

the underscore will never change positions, however sometimes it could be a space
the description can either be there or not

NBVC

My suggestion covers that possibility...

Jenedge73

ASKER

Thanks, seems to work great

NBVC

Did you get the right one?....

Jenedge73

ASKER

no i didn't. do you know how to change the solution?

nutsch

Press the Request Attention link and a mod will magically appear and reopen the question, especially if you ask nicely.

Thomas