separating multiple Proper names (uppercase letters) from a list

[Keebler]

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 Smith

Jim Bob

Jo James

Mike Peterson

Dirk 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

 

[Peter_SSs]

Thanks for the XL2BB sample.

With Eric's formula, you have the exact same formula in each cell. You need to put the first formula in, say BS1 and then drag that formula across using the Fill Handle at the bottom of the cell

With the vba function it is producing the second name because you now have the formula
=GetName($H1,COLUMNS($BY:BZ))
not
=GetName($H1,COLUMNS($BY:BY))
like you had before. Again you drag the Fill handle across.
With that particular example, the third name will be missing because it has two spaces between Brent and Lentol. My function was based on the format of the earlier samples with a single space only.

BTW, when posting XL2BB with all those irrelevant columns, hide them in your worksheet first.

 

[Keebler]

same function error

Book1.xlsm
	BY	BZ	CA	CB	CC
1	#NAME?	#NAME?	#NAME?	#NAME?	#NAME?
Sheet1

Cell Formulas
Range	Formula
BY1:CC1	BY1	=GetName($H1,COLUMNS($BY:BY))

 

[Keebler]

BY
=GetName($H1,COLUMNS($BY:BY))

BZ
=GetName($H1,COLUMNS($BY:BZ))

CA
=GetName($H1,COLUMNS($BY:CA))

and so on

 

[Peter_SSs]

same function error

.. but cell BY1 in post #19 is not showing that error. What has changed since then?

 

[Keebler]

Not sure, but after I saved the work book. the #name? error was back

 

[Peter_SSs]

Not sure, but after I saved the work book. the #name? error was back

Did you save it at *.xlsm?

Can you post an image like this that clearly shows

1. The workbook name (mine is Keebler.xlsm)
2. The module with the code where the module name is shaded light grey
3. The function code

 

[Keebler]

I like how clean the function would be, if i can figure out what I did to mess it up,
Ive now got the function to produce 2 names... the 3rd name is still illusive

CHRISTMAS MOVIES DB.xlsm
	BX	BY	BZ
350	Katie Bailey	Cindy Leclerc
0DATA0

Cell Formulas
Range	Formula
BX350:BZ350	BX350	=GetName($H350,COLUMNS($BY:BY))

 

[Keebler]

Did you save it at *.xlsm?

Can you post an image like this that clearly shows

1. The workbook name (mine is Keebler.xlsm)
2. The module with the code where the module name is shaded light grey
3. The function code

View attachment 52675

yes, it is saved as .xlsm

 

[Peter_SSs]

Just thought of another way you could get #NAME? error.
If you named the module the same as the function name, that would cause the error too.

Looks like you have it working again now though?

Regarding that third name: Do you really want that name to have two spaces in it?

 

[Peter_SSs]

Regarding that third name: Do you really want that name to have two spaces in it?

Assuming that the answer is "no", try adding this extra line into the code.

Function GetName(s As String, n As Long) As String
  s = Application.Trim(s)
  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