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]

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Column1[/td][td][/td][td=bgcolor:#70AD47]Column1[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Physician Ranchod Rakesh Naidoo (Rakesh)[/td][td][/td][td=bgcolor:#E2EFDA]Ranchod Rakesh Naidoo[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Physician Dr. Ranchod Rakesh Naidoo (Rakesh)[/td][td][/td][td]Dr. Ranchod Rakesh Naidoo[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Physician Dr. Ranchod Rakesh Naidoo Rakesh[/td][td][/td][td=bgcolor:#E2EFDA]Dr. Ranchod Rakesh Naidoo[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Dr Mike Willis Sutton III (blablabla)[/td][td][/td][td]Mike Willis Sutton III[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]ABC Wilma Buffon 123[/td][td][/td][td=bgcolor:#E2EFDA]Wilma Buffon[/td][/tr]
[/table]

 

[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.