Split full name w/single and multiple middle names

MrManBoy

New Member
Joined
May 28, 2014
Messages
37
Hi All,

Problem:

I have a data-set that has a list of full names. Splitting the first and last name is fine enough however, some of the names have multiple names between first and last name. Looking for a way (preferably w/a formula),that splits these multiple names as well splitting first and last name. Where possible, the split should occur in a single cell and not separated individually.

FYI - here is the formula I am using to split first and last name: =MID(A2&" "&A2,FIND(" ",A2)+1,LEN(A2))


Example:

Original DataDesired Data
Doe John

<tbody>
</tbody>

John Doe

<tbody>
</tbody>
Doe Jane

<tbody>
</tbody>

Jane Doe

<tbody>
</tbody>
Smith Aaron

<tbody>
</tbody>

Aaron Smith

<tbody>
</tbody>
Paul James Sam

<tbody>
</tbody>

Sam Paul James

<tbody>
</tbody>
Van Nie Sims Joe

<tbody>
</tbody>

Joe Van Nie Sims

<tbody>
</tbody>

<tbody>
</tbody>












Thanking you in advance.

:cool:
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
43,428
Office Version
365
Platform
Windows
Like this?

<b>Names</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:125px;" /><col style="width:148px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-size:10pt; ">Original Data</td><td style="font-size:10pt; ">Desired Data</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:10pt; ">Doe John</td><td style="font-size:10pt; ">John Doe</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:10pt; ">Doe Jane</td><td style="font-size:10pt; ">Jane Doe</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-size:10pt; ">Smith Aaron</td><td style="font-size:10pt; ">Aaron Smith</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-size:10pt; ">Paul James Sam</td><td style="font-size:10pt; ">Sam Paul James</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="font-size:10pt; ">Van Nie Sims Joe</td><td style="font-size:10pt; ">Joe Van Nie Sims</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B2</td><td >=LEFT(TRIM<span style=' color:008000; '>(RIGHT<span style=' color:#0000ff; '>(SUBSTITUTE<span style=' color:#ff0000; '>(A2," ",REPT<span style=' color:#804000; '>(" ",50)</span>)</span>,50)</span>)</span>&" "&A2,LEN<span style=' color:008000; '>(A2)</span>)</td></tr></table></td></tr></table>
 

MrManBoy

New Member
Joined
May 28, 2014
Messages
37
Thank you so much Peter this is perfect!!

Cheers!
 
Last edited:

MrManBoy

New Member
Joined
May 28, 2014
Messages
37
Spreadsheet Formulas
CellFormula
B2=LEFT(TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",50)),50))&" "&A2,LEN(A2))

<tbody>
</tbody>

<tbody>
</tbody>

Peter, would it be too much to ask if you could break down the formula in terms of what it's doing? Just within the substitute section, with the repeating of the 'blanks' etc. I just tried to work this out but not sure to be honest on whats happening. If not, no dramas at all. Cheers.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
43,428
Office Version
365
Platform
Windows
The SUBSTITUTE puts 50 spaces between each word.
Taking the RIGHT 50 characters will give some spaces followed by the last word.
TRIM removes the spaces leaving you with the last word.
The rest is similar to your original function.
 

MrManBoy

New Member
Joined
May 28, 2014
Messages
37
The SUBSTITUTE puts 50 spaces between each word.
Taking the RIGHT 50 characters will give some spaces followed by the last word.
TRIM removes the spaces leaving you with the last word.
The rest is similar to your original function.

Ahh ok, no worries thanks for sharing that Peter.

Cheers. (y)
 

Forum statistics

Threads
1,089,297
Messages
5,407,443
Members
403,143
Latest member
CTremblay

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top