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
Jenedge73Asked:
Who is Participating?
 
NBVCCommented:
Assuming data starts in A1,

Then in B1:E1 enter these to extract your info:

=LEFT(A1,FIND(" ",A1)-1)

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(MID(A1,FIND("Cat Code",A1)+9,255),"_"," ")," ",REPT(" ",100)),1,100))

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(MID(A1,FIND("Cat Code",A1)+9,255),"_"," ")," ",REPT(" ",100)),100,200))

=TRIM(MID(A1,FIND(D1,A1)+LEN(D1),255))

each copied down.
0
 
nutschCommented:
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
0
 
Jenedge73Author Commented:
the underscore will never change positions, however sometimes it could be a space
the description can either be there or not
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
NBVCCommented:
My suggestion covers that possibility...
0
 
Jenedge73Author Commented:
Thanks, seems to work great
0
 
NBVCCommented:
Did you get the right one?....
0
 
Jenedge73Author Commented:
no i didn't.  do you know how to change the solution?
0
 
nutschCommented:
Press the Request Attention link and a mod will magically appear and reopen the question, especially if you ask nicely.

Thomas
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.