separating multiple Proper names (uppercase letters) from a list

Keebler

Board Regular
Joined
Dec 1, 2021
Messages
140
Office Version
  1. 2021
Platform
  1. Windows
I have a list of proper names that only has spaces between their first and last names. I need a formula that can separate these names into adjacent cells...
ex:
Jim BobJo JamesMike PettersonDirk Smith Jim Bob Jo James Mike Peterson Dirk Smith

Jim BobJo JamesMike PetersonDirk SmithJim BobJo JamesMike PetersonDirk Smith
does that make any sense?

thank you
Ken

to add injury to insult.. there are a few times that I have seen Proper names with McFlury or people with 2 last names Like JoEllen Smith-Peterson

thank you
 
Last edited by a moderator:

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Welcome to the MrExcel forum!

The bad news is that you're going to be at least a little unsatisfied. We've looked at many similar situations, where someone is trying to parse out people's names from a list. And like your exception list with McFlury or hyphens, there are always going to be names that can't be handled with a formula, or even a macro. I spent a lot of time writing a macro to do that, and even years later, I was still encountering exceptions.

Still, you might try this:

Book1
ABCDEFG
1Jim BobJo JamesMike PetersonDirk SmithJim BobJo JamesMike PetersonDirk Smith 
2Max PowerJames KirkJane Smith-PetersonHomer SimpsonMax PowerJames KirkJane Smith-PetersonHomer Simpson 
3Amelia EarhartJoEllen SmithNeil ArmstrongAmelia EarhartJoEllen SmithNeil Armstrong 
Sheet16
Cell Formulas
RangeFormula
C1:G3C1=LEFT(MID($A1,LEN(CONCAT($B1:B1))+1,999),AGGREGATE(15,6,ROW(INDIRECT(LEN(CONCAT($B1:B1))+2&":"&LEN($A1)))/ISNUMBER(FIND(MID($A1,ROW(INDIRECT(LEN(CONCAT($B1:B1))+2&":"&LEN($A1))),1),"abcdefghijklmnopqrstuvwxyz"))/ISNUMBER(FIND(MID($A1&"A",ROW(INDIRECT(LEN(CONCAT($B1:B1))+3&":"&LEN($A1)+1)),1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")),1)-LEN(CONCAT($B1:B1)))


Notice that column B must be empty. Put the formula in C2 and drag across and down. You'll still have to manually scan the list for exceptions. This formula handles hyphenated last names, but not names with multiple capital letters.
 
Upvote 0
I'll give it a try and report back - Thank you for your quick reply :)
 
Upvote 0
The bad news is that you're going to be at least a little unsatisfied.
Unfortunately, I agree completely with Eric.

Take his A3 text for example. Whilst we might take a reasonable human guess at splitting "EarhartJoEllen" into "Earhart" and "JoEllen", there is no logical reason the split couldn't be "EarhartJo" and "Ellen".

This is the best that I can come up with. You can see obvious problems with it too, particularly in the last two rows. What I have done is mark any results that do not contain exactly two upper case letters. Those results, and in fact that whole row, should be checked & perhaps amended manually?

This is a user-defined function. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below (you can use the icon at the top right of the code pane below) into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

VBA Code:
Function GetName(s As String, n As Long) As String
  With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "[A-Z][^ ]*?[a-z] [A-Z].+?[a-z](?=([A-Z][^A-Z]{2})|$)"
    If n - 1 < .Execute(s).Count Then GetName = .Execute(s)(n - 1)
    .Pattern = "[A-Z]"
    If Len(GetName) > 0 And .Execute(GetName).Count <> 2 Then GetName = GetName & "*"
  End With
End Function

Keebler.xlsm
ABCDEF
1Jim BobJo JamesMike PetersonDirk SmithJim BobJo JamesMike PetersonDirk Smith 
2Max PowerJames KirkJane Smith-PetersonHomer SimpsonMax PowerJames KirkJane Smith-Peterson*Homer Simpson 
3Amelia EarhartJoEllen SmithNeil ArmstrongAmelia EarhartJo*Ellen SmithNeil Armstrong  
4Tim JonesFred McFlurryJim-Bob Smith-JonesTim JonesFred McFlurry*Jim-Bob Smith-Jones*  
5Henri MatisseVincent van GoghEdvard MunchHenri MatisseGoghEdvard Munch*   
6James Van Der BeekMichael van der VeenJames Van Der Beek*    
Sheet1
Cell Formulas
RangeFormula
B1:F6B1=GetName($A1,COLUMNS($B:B))
 
Upvote 0
Welcome to the MrExcel forum!

The bad news is that you're going to be at least a little unsatisfied. We've looked at many similar situations, where someone is trying to parse out people's names from a list. And like your exception list with McFlury or hyphens, there are always going to be names that can't be handled with a formula, or even a macro. I spent a lot of time writing a macro to do that, and even years later, I was still encountering exceptions.

Still, you might try this:

Book1
ABCDEFG
1Jim BobJo JamesMike PetersonDirk SmithJim BobJo JamesMike PetersonDirk Smith 
2Max PowerJames KirkJane Smith-PetersonHomer SimpsonMax PowerJames KirkJane Smith-PetersonHomer Simpson 
3Amelia EarhartJoEllen SmithNeil ArmstrongAmelia EarhartJoEllen SmithNeil Armstrong 
Sheet16
Cell Formulas
RangeFormula
C1:G3C1=LEFT(MID($A1,LEN(CONCAT($B1:B1))+1,999),AGGREGATE(15,6,ROW(INDIRECT(LEN(CONCAT($B1:B1))+2&":"&LEN($A1)))/ISNUMBER(FIND(MID($A1,ROW(INDIRECT(LEN(CONCAT($B1:B1))+2&":"&LEN($A1))),1),"abcdefghijklmnopqrstuvwxyz"))/ISNUMBER(FIND(MID($A1&"A",ROW(INDIRECT(LEN(CONCAT($B1:B1))+3&":"&LEN($A1)+1)),1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")),1)-LEN(CONCAT($B1:B1)))


Notice that column B must be empty. Put the formula in C2 and drag across and down. You'll still have to manually scan the list for exceptions. This formula handles hyphenated last names, but not names with multiple capital letters.
First, let me apologize for the delay getting back here, got called out of town... I am sorry...

I got the formula in my SS and the results were less than spectacular..
#NUM!​
#NUM!​
#NUM!​
#NUM!​
#NUM!​
#NUM!​
#NUM!​
I realize that I'm not as profeccient in formulas as you guys... so I have no doubt *I* did something wrong..
 
Upvote 0
Unfortunately, I agree completely with Eric.

Take his A3 text for example. Whilst we might take a reasonable human guess at splitting "EarhartJoEllen" into "Earhart" and "JoEllen", there is no logical reason the split couldn't be "EarhartJo" and "Ellen".

This is the best that I can come up with. You can see obvious problems with it too, particularly in the last two rows. What I have done is mark any results that do not contain exactly two upper case letters. Those results, and in fact that whole row, should be checked & perhaps amended manually?

This is a user-defined function. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below (you can use the icon at the top right of the code pane below) into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

VBA Code:
Function GetName(s As String, n As Long) As String
  With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "[A-Z][^ ]*?[a-z] [A-Z].+?[a-z](?=([A-Z][^A-Z]{2})|$)"
    If n - 1 < .Execute(s).Count Then GetName = .Execute(s)(n - 1)
    .Pattern = "[A-Z]"
    If Len(GetName) > 0 And .Execute(GetName).Count <> 2 Then GetName = GetName & "*"
  End With
End Function

Keebler.xlsm
ABCDEF
1Jim BobJo JamesMike PetersonDirk SmithJim BobJo JamesMike PetersonDirk Smith 
2Max PowerJames KirkJane Smith-PetersonHomer SimpsonMax PowerJames KirkJane Smith-Peterson*Homer Simpson 
3Amelia EarhartJoEllen SmithNeil ArmstrongAmelia EarhartJo*Ellen SmithNeil Armstrong  
4Tim JonesFred McFlurryJim-Bob Smith-JonesTim JonesFred McFlurry*Jim-Bob Smith-Jones*  
5Henri MatisseVincent van GoghEdvard MunchHenri MatisseGoghEdvard Munch*   
6James Van Der BeekMichael van der VeenJames Van Der Beek*    
Sheet1
Cell Formulas
RangeFormula
B1:F6B1=GetName($A1,COLUMNS($B:B))
Please except my apologies for the delay getting a responce back to you. thank you for your input..

I created the module and copied the VBA code... however, I got errors (yes the workbook is saved .xlsm)
#NAME?​
#NAME?​
#NAME?​
#NAME?​
#NAME?​
I'm not sure what I did wrong
 
Upvote 0
I created the module and copied the VBA code... however, I got errors (yes the workbook is saved .xlsm)
#NAME?​
#NAME?​
#NAME?​
#NAME?​
#NAME?​
I'm not sure what I did wrong
Typically, that error would come if the name of the function in the vba ..

1638617777422.png


.. is not exactly the same as the function name used in the worksheet

1638617842484.png
 
Upvote 0
Typically, that error would come if the name of the function in the vba ..

View attachment 52667

.. is not exactly the same as the function name used in the worksheet

View attachment 52668
How do I correct this?
Did I copy it wrong?
Did I execute it incorrectly?
here is my syntax =GetName($H349,COLUMN($BY:BY))
I had to adjust it to fit my worksheet, did i do it wrong?
 
Upvote 0
In relation to your #NUM! errors when trying Eric's formula, one reason would be that your formula could be pointed at an empty cell.
Try starting a new, blank worksheet and then ..
  • Click this icon at the top left of Eric's mini-sheet:
    1638618065678.png


  • Select cell A1 in the blank worksheet and Paste
 
Upvote 0
here is my syntax =GetName($H349,COLUMN($BY:BY))

Are you sure that is your syntax? It should be
=GetName($H349,COLUMNS($BY:BY))

But that missing "S" should not cause the #NAME? error

Another reason you could get #NAME? error is if you have the function code pasted more than once in the vba module. Check that too.
 
Upvote 0

Forum statistics

Threads
1,212,933
Messages
6,110,752
Members
448,295
Latest member
Uzair Tahir Khan

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top