Extract Middle Words from a Text String with Multiple Dots

[justmecat]

Hi there,

I've been searching for an optimal excel formula that could help me to solve the following problem, but I've been unable to find a right solution yet; so I hope you guys can help me!

The example would be:

black.blue.red.yellow.green.white

I'd like to extract just "blue.red.yellow.green" to the following spreadsheet column, keeping the intermediate dots, but getting rid of the first/last words and their succeeding or preceding dots.

I've tried using RIGHT, LEFT, MID formulas unsuccessfully, so I'd really appreciate if you guys can shed some light.

Thank you!

 

[Andrew Poulsom]

Welcome to MrExcel.

How about?

=MID(A1,FIND(".",A1)+1,FIND(CHAR(1),SUBSTITUTE(A1,".",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1,".",""))))-(FIND(".",A1)+1))

 

[Jborg]

Rather long....but should work.

=MID(SUBSTITUTE(B21,MID(B21,1,SEARCH(".",B21,1)),""),1,LEN(SUBSTITUTE(B21,MID(B21,1,SEARCH(".",B21,1)),""))-LEN(MID(SUBSTITUTE(B21,MID(B21,1,SEARCH(".",B21,1)),""),MATCH(2,1/--(MID(SUBSTITUTE(B21,MID(B21,1,SEARCH(".",B21,1)),""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(B21,MID(B21,1,SEARCH(".",B21,1)),"")))),1)=".")),LEN(SUBSTITUTE(B21,MID(B21,1,SEARCH(".",B21,1)),"")))))

 

[justmecat]

Thanks so much Andrew! It's exactly what I was looking for and it worked perfectly!! Thanks again!

 

[bw21638]

This is very helpful and I'm sorry, but I'm having a hard time understanding the individual components. I have a similar scenario as follows:

John Smith ...................................Marketing Manager .................................................................................................................ABC Co

Jim Jones.......................................Senior Vice President .................................................................................HIJ Inc

Julie Johnson .....................................HR Manager............................................................................................................................. XYZ LLC

<colgroup><col></colgroup><tbody>
</tbody>

With variable lengths of ellipses, how do I grab the middle part (job title) and the right part (the company name)?

Appreciate your help!

 

[Andrew Poulsom]

Welcome to MrExcel.

This example uses helper columns. They are not strictly necessary but the formula would become very long without them.

Excel 2010
	A	B	C	D	E
1	John Smith ........................Marketing Manager .............................................ABC Co	John|Smith| Marketing|Manager| ABC|Co	Marketing|Manager| ABC|Co	Marketing Manager	ABC Co
2	Jim Jones.......................................Senior Vice President ...............HIJ Inc	Jim|Jones Senior|Vice|President| HIJ|Inc	Senior|Vice|President| HIJ|Inc	Senior Vice President	HIJ Inc
3	Julie Johnson ......HR Manager................................................................. XYZ LLC	Julie|Johnson| HR|Manager |XYZ|LLC	HR|Manager |XYZ|LLC	HR Manager	XYZ LLC
Sheet3

Cell Formulas
Range	Formula
B1	=TRIM(SUBSTITUTE(SUBSTITUTE(A1," ","|"),"."," "))
C1	=TRIM(MID(B1,FIND(" ",B1),255))
D1	=TRIM(SUBSTITUTE(LEFT(C1,FIND(" ",C1)-1),"|"," "))
E1	=TRIM(SUBSTITUTE(RIGHT(C1,LEN(C1)-FIND(" ",C1)),"|"," "))

 

[eriikitubbs]

Hi,

I think I have quite a similar problem as the original poster of this thread.

I have for example the following two strings, each in a cell:

I100367/text= "NEW OVARY" /MainGroup= 0/data= ""/display= ""/rep_int= ""/rep_ext= ""/prec= I100366/menu= M2415919103/with_comment= 0/with_site= 0/with_count= 0/default= 0/def_comment= ""/def_count= 1/calc= ""/exclusive= 0;
I100095/text= cervix /MainGroup= 0/data= ""/display= ""/rep_int= ""/rep_ext= ""/prec= I100093/menu= M2415919103/with_comment= 0/with_site= 0/with_count= 0/default= 0/def_comment= ""/def_count= 1/calc= ""/exclusive= 0;

And I would like to extract from the 1st cell "NEW OVARY" and for the 2nd cell "cervix"

That is, I want to extract out of the string everything after "/text= " (this is a constant) and before " /MainGroup=" (this is also a constant)

I've tried with FIND, MID, RIGHT, LEFT and I can successfully extract a certain amount of characters after the first constant but I don't know how to implement the 2nd constant into the formula.

Thank you if anyone has any suggestions!

 

[Andrew Poulsom]

Try eg:

=LEFT(MID(A1,FIND("=",A1)+2,255),FIND("/",MID(A1,FIND("=",A1)+2,255))-2)

 

[eriikitubbs]

Thank you, Andrew! That worked perfectly.

 

[István Hirsch]

Another version:

=MID(A1,FIND("=",A1)+2,FIND(" /",A1)-FIND("=",A1)-2)