# Find text in String

#### ilcaa

##### Well-known Member
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?

### Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
ilcaa,

Here is one way based on the sample text display:

Excel 2007
AB
1LiborCore: Ann, Fred Shumway: Dan Karp & Randy BarbourAnn, Fred
Sheet1
Cell Formulas
RangeFormula
B1=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",100)),100,100))&" "&TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",100)),200,100))

will there always be just two names?

no, there could be multiple name

the 2 cornerstones is

LiberCore: text to extract RandomCompanyName:

ilcaa,

Here is one way based on the sample text display:

Excel 2007
AB
1LiborCore: Ann, Fred Shumway: Dan Karp & Randy BarbourAnn, Fred
Sheet1
Cell Formulas
RangeFormula
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)

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)

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

I cant find the tweak to fix it

thanks rick

I found it, lowered 12 to 11

works nicely, thank you!

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.

Replies
4
Views
120
Replies
3
Views
191
Replies
20
Views
838
Replies
6
Views
505
Replies
7
Views
255

1,203,172
Messages
6,053,904
Members
444,692
Latest member
Queendom

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back