Excel formula to extract any text in between the first and last word of a cell

[nbwest76]

I'm a bit stuck, and was unable to find what I'm looking for by searching. I am trying to break out a name field. I am able to use a formula to get both the first word and last word of a cell to identify the first and last name, but I would like to find a way to use a formula to get all of the text in between the first and last name of a cell if possible. I can't use the Text to Columns Delimited option because some names have more than one middle name.

Thank you

 

[hiker95]

nbwest76,

You have not given us any examples of the names?


How about something like this?

Excel 2007
	A	B
1	MICHAEL JOHN H ANTHONY SMITH	JOHN H ANTHONY
Sheet1

Cell Formulas
Range	Formula
B1	=IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))=1,"",MID(A1,FIND(" ",A1,1)+1,FIND(MID(A1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,255),A1,1)-2-FIND(" ",A1,1)))

 

[hiker95]

nbwest76,

Welcome to the MrExcel forum.

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?


You have not given us any examples of the names?


How about something like this?

Excel 2007
	A	B
1	MICHAEL JOHN H ANTHONY SMITH	JOHN H ANTHONY
Sheet1

Cell Formulas
Range	Formula
B1	=IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))=1,"",MID(A1,FIND(" ",A1,1)+1,FIND(MID(A1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,255),A1,1)-2-FIND(" ",A1,1)))

 

[hiker95]

nbwest76,

More sample names/text using the same formula:

Excel 2007
	A	B
1	I'm a bit stuck, and was unable to find what I'm looking for by searching.	a bit stuck, and was unable to find what I'm looking for by
2	I am trying to break out a name field.	am trying to break out a name
3	Excel formula to extract any text in between the first and last word of a cell	formula to extract any text in between the first and last word of a
4	MICHAEL JOHN H ANTHONY SMITH	JOHN H ANTHONY
5	Michael H Smith	H
6	Michael Smith
7	Michael John H SMITH	John H
Sheet1

Cell Formulas
Range	Formula
B1	=IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))=1,"",MID(A1,FIND(" ",A1,1)+1,FIND(MID(A1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,255),A1,1)-2-FIND(" ",A1,1)))

 

[mikerickson]

Try
=TRIM(LEFT(SUBSTITUTE(A1," ",REPT(" ",255)),255) & RIGHT(SUBSTITUTE(A1," ",REPT(" ",255)),255))

 

[nbwest76]

Thanks Hiker95,

this seems to be what I'm looking for. My challenge is that I'm using MS Access to export to an Excel 2010 spreadsheet with the full name in one field. I need to take that full name field and break it out into 3 parts; First, Middle initial, and Last.

Also another problem here is that this full name field is a manually entered field so there are a lot of different combination of names that can appear. Here are a few examples...

RAYMOND FRANK KALINOSKE SR
JOHN C DIBLLE
REMINGIO L DOLOPI JR
MR JEFFEREY D ROSE

My plan is to run a function that will erase the suffix' then use this code to break out the middle name.

[TABLE="width: 191"]
<TBODY>[TR]
[TD]</SPAN>[/TD]
[/TR]
[TR]
[TD]</SPAN>[/TD]
[/TR]
[TR]
[TD]</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL></COLGROUP>[/TABLE]

 

[hiker95]

nbwest76,

Thanks for the feedback.

You are very welcome. Glad I could help.

My plan is to run a function that will erase the suffix' then use this code to break out the middle name.

So that we can get it right on the next try:


Can we have a list of the prefix's, and, suffix's?


Can you post a screenshot of the actual raw data worksheet?

And, can you post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post a small screen shot try one of the following:

Excel Jeanie
Download

MrExcel HTML Maker
https://onedrive.live.com/?cid=8cffdec0ce27e813&sc=documents&id=8CFFDEC0CE27E813!189

Borders-Copy-Paste
http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045

To test the above:
Test Here


Or, you can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.