Find text in String

[ilcaa]

have another string puzzle that I cant get! (I hate text manipulation)


LiborCore: Ann, Fred Shumway: Dan Karp & Randy Barbour

there are some constants,

it always starts with LiborCore:
there is always a second colon :

I need the name between LiborCore and the other company name (Shumway: )

this would return Ann, Fred

(not Shumway, its part of the second Colon/company name)
possible?

 

[hiker95]

ilcaa,

Here is one way based on the sample text display:

Excel 2007
	A	B
1	LiborCore: Ann, Fred Shumway: Dan Karp & Randy Barbour	Ann, Fred
Sheet1

Cell Formulas
Range	Formula
B1	=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",100)),100,100))&" "&TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",100)),200,100))

 

[hateme28]

will there always be just two names?

 

[ilcaa]

no, there could be multiple name

the 2 cornerstones is

LiberCore: text to extract RandomCompanyName:

 

[ilcaa]

ilcaa,

Here is one way based on the sample text display:

Excel 2007
	A	B
1	LiborCore: Ann, Fred Shumway: Dan Karp & Randy Barbour	Ann, Fred
Sheet1

Cell Formulas
Range	Formula
B1	=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",100)),100,100))&" "&TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",100)),200,100))

thanks

but is only works when Ann, Fred are the text values,

I have random names, 2 or 3 or 4 names , etc (There are 4500 rows to do this for)

 

[Rick Rothstein]

Give this formula a try...

=MID(SUBSTITUTE(LEFT(A1,FIND(":",A1,FIND(":",A1)+1))," "&TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND(":",A1,FIND(":",A1)+1))," ",REPT(" ",99)),99)),""),12,999)

 

[ilcaa]

Give this formula a try...

=MID(SUBSTITUTE(LEFT(A1,FIND(":",A1,FIND(":",A1)+1))," "&TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND(":",A1,FIND(":",A1)+1))," ",REPT(" ",99)),99)),""),12,999)

It almost works!

it does grab everything before the second company nameColon(Shumway: )
but it leaves off the first letter of the First word

so a get nn, Fred, ross
instead of Ann, Fred, ross

I cant find the tweak to fix it

 

[ilcaa]

thanks rick

I found it, lowered 12 to 11

works nicely, thank you!

 

[Rick Rothstein]

thanks rick

I found it, lowered 12 to 11

works nicely, thank you!

You are welcome. I saw that problem, but apparently forgot to copy the revised formula into the Clipboard (although I could swear I remember having done so) before I pasted it into my response.