Formula to remove first and last word

[DHayes]

Good Day,
I have searched and I can't find a formula to remove the first word and last word in excel.

What I have
Physician Ranchod Rakesh Naidoo (Rakesh)

Result I would like to achieve.
Ranchod Rakesh Naidoo

 

[jmacleary]

Hi there.

I cobbled this formula together from 2 others:

=LEFT(MID(A1,FIND(" ",A1)+1,256),FIND("^^",SUBSTITUTE(MID(A1,FIND(" ",A1)+1,256)," ","^^",LEN(MID(A1,FIND(" ",A1)+1,256))-LEN(SUBSTITUTE(MID(A1,FIND(" ",A1)+1,256)," ",""))))-1)

where A1 is the source data.

Regards
John

 

[DHayes]

Thank you it works great.

 

[jtakw]

Hi,

If the Last word may or may Not be contained in Brackets, use B1 formula.
If the Last word is Always contained in Brackets, use C1 formula.

Book1
	A	B	C
1	Physician Ranchod Rakesh Naidoo (Rakesh)	Ranchod Rakesh Naidoo	Ranchod Rakesh Naidoo
2	Physician Dr. Ranchod Rakesh Naidoo (Rakesh)	Dr. Ranchod Rakesh Naidoo	Dr. Ranchod Rakesh Naidoo
3	Physician Dr. Ranchod Rakesh Naidoo Rakesh	Dr. Ranchod Rakesh Naidoo
Sheet546

Cell Formulas
Range	Formula
B1	=TRIM(LEFT(SUBSTITUTE(MID(A1,FIND(" ",A1),255)," ",REPT(" ",255),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))),255))
C1	=MID(A1,FIND(" ",A1)+1,FIND("(",A1)-FIND(" ",A1)-2)

 

[sandy666]

or PowerQuery

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    Extract = Table.TransformColumns(Table.TransformColumns(Source, {{"Column1", each Text.AfterDelimiter(_, " "), type text}}), {{"Column1", each Text.BeforeDelimiter(_, " ", {0, RelativePosition.FromEnd}), type text}})
in
    Extract[/SIZE]

Column1		Column1
Physician Ranchod Rakesh Naidoo (Rakesh)		Ranchod Rakesh Naidoo
Physician Dr. Ranchod Rakesh Naidoo (Rakesh)		Dr. Ranchod Rakesh Naidoo
Physician Dr. Ranchod Rakesh Naidoo Rakesh		Dr. Ranchod Rakesh Naidoo
Dr Mike Willis Sutton III (blablabla)		Mike Willis Sutton III
ABC Wilma Buffon 123		Wilma Buffon

 

[DHayes]

Thank you jtwk and Sandy666,
Both solutions work excellent.

 

[Rick Rothstein]

Here is another formula that should work whether the last word is in parentheses or not...

=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",100)),100,300))

 

[DHayes]

Thanks Rick it removes the 1st word and not the last.

 

[sandy666]

You are welcome & have a nice day

 

[Tom.Jones]

Thanks Rick it removes the 1st word and not the last.

Rick's formula works excellent.