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

 

[Keebler]

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: View attachment 52669
  
  
• Select cell A1 in the blank worksheet and Paste

again, I modified it to fit my worksheet
here is my syntax =IF($H349="","",LEFT(MID($H349,LEN(CONCAT($BR349:BR349))+1,999),AGGREGATE(15,6,ROW(INDIRECT(LEN(CONCAT($BR349:BR349))+2&":"&LEN($H349)))/ISNUMBER(FIND(MID($H349,ROW(INDIRECT(LEN(CONCAT($BR349:BR349))+2&":"&LEN($H349))),1),"abcdefghijklmnopqrstuvwxyz"))/ISNUMBER(FIND(MID($H349&"A",ROW(INDIRECT(LEN(CONCAT($BR349:BR349))+3&":"&LEN($H349)+1)),1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")),1)-LEN(CONCAT($BR349:BR349))))
did i do that incorrectly?
the data to be sorted is stored in the H column (currently on row 349), there is other data in all the columns until column BQ (which is why it is allowing the open column of BR)

 

[Keebler]

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.

I did go ahead and entered the missing "s".. to no avail,
and confirmed there is only one instance of the VBA module with THIS code

 

[Keebler]

I found the issue with the #num! error, the cell with the data had a pair of "()", with those removed, it did kinda sort... only the first name of the FIRST person in the list

 

[Peter_SSs]

here is my syntax =IF($H349="","",LEFT(MID($H349,LEN(CONCAT($BR349:BR349))+1,999),AGGREGATE(15,6,ROW(INDIRECT(LEN(CONCAT($BR349:BR349))+2&":"&LEN($H349)))/ISNUMBER(FIND(MID($H349,ROW(INDIRECT(LEN(CONCAT($BR349:BR349))+2&":"&LEN($H349))),1),"abcdefghijklmnopqrstuvwxyz"))/ISNUMBER(FIND(MID($H349&"A",ROW(INDIRECT(LEN(CONCAT($BR349:BR349))+3&":"&LEN($H349)+1)),1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")),1)-LEN(CONCAT($BR349:BR349))))
did i do that incorrectly?

That looks okay to me. Here is my test with columns I:BQ hidden & your formula starting in BS349
Can you post the same with XL2BB?

Keebler.xlsm
	H																																																														BR	BS	BT	BU	BV	BW
349	Jim BobJo JamesMike PetersonDirk Smith																																																															Jim Bob	Jo James	Mike Peterson	Dirk Smith
Sheet1

Cell Formulas
Range	Formula
BS349:BW349	BS349	=IF($H349="","",LEFT(MID($H349,LEN(CONCAT($BR349:BR349))+1,999),AGGREGATE(15,6,ROW(INDIRECT(LEN(CONCAT($BR349:BR349))+2&":"&LEN($H349)))/ISNUMBER(FIND(MID($H349,ROW(INDIRECT(LEN(CONCAT($BR349:BR349))+2&":"&LEN($H349))),1),"abcdefghijklmnopqrstuvwxyz"))/ISNUMBER(FIND(MID($H349&"A",ROW(INDIRECT(LEN(CONCAT($BR349:BR349))+3&":"&LEN($H349)+1)),1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")),1)-LEN(CONCAT($BR349:BR349))))

 

[Keebler]

the #NAME? error still persists

 

[Peter_SSs]

I did go ahead and entered the missing "s".. to no avail,
and confirmed there is only one instance of the VBA module with THIS code

Can you make up a small sample workbook with the vba code in it and a small amount of data in column H (no sensitive data) and the function entered in the relevant cells? If that still shows #NAME, can you upload that sample file to DropBox or OneDrive or Google Drive etc and provide a shared link here in the forum so that I can take a look?

 

[Keebler]

So, here is the data

Can you make up a small sample workbook with the vba code in it and a small amount of data in column H (no sensitive data) and the function entered in the relevant cells? If that still shows #NAME, can you upload that sample file to DropBox or OneDrive or Google Drive etc and provide a shared link here in the forum so that I can take a look?

sure, one moment.. preparing now

 

[Keebler]

I DID create the black workbook and created the new module, etc.. placed data in the EXACT same cells.. NO major errors except
the formula only produces the FIRST name, no others.. and the function only produces the 2 name.. no others

 

[Keebler]

Book1.xlsm
	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR	BS	BT	BU	BV	BW	BX	BY	BZ	CA	CB	CC
1	Katie BaileyCindy LeclercBrent Lentol																																																													Katie Bailey		Katie Bailey	Katie Bailey	Katie Bailey	Katie Bailey			Cindy Leclerc
Sheet1

Cell Formulas
Range	Formula
BQ1	BQ1	=IF($H1="","",LEFT(MID($H1,LEN(CONCAT($BR1:BR1))+1,999),AGGREGATE(15,6,ROW(INDIRECT(LEN(CONCAT($BR1:BR1))+2&":"&LEN($H1)))/ISNUMBER(FIND(MID($H1,ROW(INDIRECT(LEN(CONCAT($BR1:BR1))+2&":"&LEN($H1))),1),"abcdefghijklmnopqrstuvwxyz"))/ISNUMBER(FIND(MID($H1&"A",ROW(INDIRECT(LEN(CONCAT($BR1:BR1))+3&":"&LEN($H1)+1)),1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")),1)-LEN(CONCAT($BR1:BR1))))
BS1	BS1	=IF($H1="","",LEFT(MID($H1,LEN(CONCAT($BR1:BR1))+1,999),AGGREGATE(15,6,ROW(INDIRECT(LEN(CONCAT($BR1:BR1))+2&":"&LEN($H1)))/ISNUMBER(FIND(MID($H1,ROW(INDIRECT(LEN(CONCAT($BR1:BR1))+2&":"&LEN($H1))),1),"abcdefghijklmnopqrstuvwxyz"))/ISNUMBER(FIND(MID($H1&"A",ROW(INDIRECT(LEN(CONCAT($BR1:BR1))+3&":"&LEN($H1)+1)),1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")),1)-LEN(CONCAT($BR1:BR1))))
BT1	BT1	=IF($H1="","",LEFT(MID($H1,LEN(CONCAT($BR1:BR1))+1,999),AGGREGATE(15,6,ROW(INDIRECT(LEN(CONCAT($BR1:BR1))+2&":"&LEN($H1)))/ISNUMBER(FIND(MID($H1,ROW(INDIRECT(LEN(CONCAT($BR1:BR1))+2&":"&LEN($H1))),1),"abcdefghijklmnopqrstuvwxyz"))/ISNUMBER(FIND(MID($H1&"A",ROW(INDIRECT(LEN(CONCAT($BR1:BR1))+3&":"&LEN($H1)+1)),1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")),1)-LEN(CONCAT($BR1:BR1))))
BU1	BU1	=IF($H1="","",LEFT(MID($H1,LEN(CONCAT($BR1:BR1))+1,999),AGGREGATE(15,6,ROW(INDIRECT(LEN(CONCAT($BR1:BR1))+2&":"&LEN($H1)))/ISNUMBER(FIND(MID($H1,ROW(INDIRECT(LEN(CONCAT($BR1:BR1))+2&":"&LEN($H1))),1),"abcdefghijklmnopqrstuvwxyz"))/ISNUMBER(FIND(MID($H1&"A",ROW(INDIRECT(LEN(CONCAT($BR1:BR1))+3&":"&LEN($H1)+1)),1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")),1)-LEN(CONCAT($BR1:BR1))))
BV1	BV1	=IF($H1="","",LEFT(MID($H1,LEN(CONCAT($BR1:BR1))+1,999),AGGREGATE(15,6,ROW(INDIRECT(LEN(CONCAT($BR1:BR1))+2&":"&LEN($H1)))/ISNUMBER(FIND(MID($H1,ROW(INDIRECT(LEN(CONCAT($BR1:BR1))+2&":"&LEN($H1))),1),"abcdefghijklmnopqrstuvwxyz"))/ISNUMBER(FIND(MID($H1&"A",ROW(INDIRECT(LEN(CONCAT($BR1:BR1))+3&":"&LEN($H1)+1)),1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")),1)-LEN(CONCAT($BR1:BR1))))
BY1:CC1	BY1	=GetName($H1,COLUMNS($BY:BZ))

 

[Keebler]

so, after I dragged the formula across the other cells.. it populated CORRECTLY

Book1.xlsm
	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR	BS	BT	BU	BV
1	Katie BaileyCindy LeclercBrent Lentol																																																													Katie Bailey		Katie Bailey	Cindy Leclerc	Brent Lentol
Sheet1

Cell Formulas
Range	Formula
BQ1	BQ1	=IF($H1="","",LEFT(MID($H1,LEN(CONCAT($BR1:BR1))+1,999),AGGREGATE(15,6,ROW(INDIRECT(LEN(CONCAT($BR1:BR1))+2&":"&LEN($H1)))/ISNUMBER(FIND(MID($H1,ROW(INDIRECT(LEN(CONCAT($BR1:BR1))+2&":"&LEN($H1))),1),"abcdefghijklmnopqrstuvwxyz"))/ISNUMBER(FIND(MID($H1&"A",ROW(INDIRECT(LEN(CONCAT($BR1:BR1))+3&":"&LEN($H1)+1)),1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")),1)-LEN(CONCAT($BR1:BR1))))
BS1:BV1	BS1	=IF($H1="","",LEFT(MID($H1,LEN(CONCAT($BR1:BR1))+1,999),AGGREGATE(15,6,ROW(INDIRECT(LEN(CONCAT($BR1:BR1))+2&":"&LEN($H1)))/ISNUMBER(FIND(MID($H1,ROW(INDIRECT(LEN(CONCAT($BR1:BR1))+2&":"&LEN($H1))),1),"abcdefghijklmnopqrstuvwxyz"))/ISNUMBER(FIND(MID($H1&"A",ROW(INDIRECT(LEN(CONCAT($BR1:BR1))+3&":"&LEN($H1)+1)),1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")),1)-LEN(CONCAT($BR1:BR1))))

but the function is still giving me the #name? error