# Formula to remove first and last word

#### DHayes

##### Board Regular
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

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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

Thank you it works great.

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
ABC
1Physician Ranchod Rakesh Naidoo (Rakesh)Ranchod Rakesh NaidooRanchod Rakesh Naidoo
2Physician Dr. Ranchod Rakesh Naidoo (Rakesh)Dr. Ranchod Rakesh NaidooDr. Ranchod Rakesh Naidoo
3Physician Dr. Ranchod Rakesh Naidoo RakeshDr. Ranchod Rakesh Naidoo
Sheet546
Cell Formulas
RangeFormula
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)

or PowerQuery

Code:
``````[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

Thank you jtwk and Sandy666,
Both solutions work excellent.

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

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

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

Last edited:
You are welcome & have a nice day

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

Rick's formula works excellent.

Replies
3
Views
139
Replies
13
Views
288
Replies
1
Views
150
Replies
1
Views
128
Replies
1
Views
183

1,196,047
Messages
6,013,085
Members
441,747
Latest member
darkman77

### 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