I need to parse out certian text. [SOLVED]

[daniels012]

I need to split this out
Name They are all QC(Quality Control) then Rank
In cell A1:
Anderson, Bob QC, Top Gun
I need in B1:
Anderson, Bob
In C1:
Top Gun

What could I use to get these results? The QC, is consistant every time in each set of text.

Thank You for help!!
Michael

 

[ExcelChampion]

In B1:
=TRIM(LEFT(A1,FIND(" ",A1,FIND(" ",A1)+1)))

In C1:
=SUBSTITUTE(A1,LEFT(A1,FIND("QC, ",A1)+3),"")

 

[ravishankar]

Hi
B1= Left(A1,search("QC",A1)-2)
C1 = right(A1, Len(a1)-search("QC",A1)-2)
Ravi

 

[unnilennium78]

Hi Excel Champion.....

I think B1 chould be changed to
=TRIM(LEFT(A1,FIND("QC,",A1)-1))

because if in any case there is 3 spacing before the word QC, then there will be an error in the sorting....

 

[unnilennium78]

There is another alternative way.....

B1
=MID(A1,1,SEARCH("QC,",A1,1)-1)

C1
=MID(A1,SEARCH("QC,",A1,1)+3,255)

but there is a limitation that C1 will only output till 255 char.......

 

[unnilennium78]

I still think Excel Champion & ravishankar has the best solution..... cheers to both of u....

 

[Peter_SSs]

A fairly quick non-formula option maybe?

1. Select the column and do a Find/Replace, finding " QC, " and replacing with "@" (without the "" which I just included to identify the spaces in the Find text)
2. Data|Text to Columns using @ as the delimiter.

 

[daniels012]

Thank You everyone!
They all seemed to work quite well.

You know Peter, that worked really well. It really was very simple and clean!!

Thank You again,
Michael