Find text in String

ilcaa

Well-known Member
Joined
May 25, 2005
Messages
751
Office Version
  1. 365
Platform
  1. Windows
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?
 
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))
 
Upvote 0
no, there could be multiple name

the 2 cornerstones is

LiberCore: text to extract RandomCompanyName:
 
Upvote 0
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)
 
Upvote 0
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)
 
Upvote 0
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
 
Upvote 0
thanks rick

I found it, lowered 12 to 11

works nicely, thank you!
 
Upvote 0
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.
 
Upvote 0

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top