Splitting first and last names without spaces

[MSP]

Good morning,

I recently pulled a list of golfer names from a folder and I need to add a space between the first and last names. The first names are all in lowercase and the last names are all in uppercase:

tigerWOODS
philMICKELSON
jordanSPIETH
maxHOMA

I found a formula in this thread and it's very close to what I'm looking for. But I want to put the space between the last lowercase and first uppercase letters and it has to work for a list of 200 people. How would I do that?

Thanks,

MSP

 

[JEC]

How about

Function jec(xStr As String) As String
 With CreateObject("vbscript.regexp")
   .Pattern = "([a-z])([A-Z])"
   jec = .Replace(xStr, "$1 $2")
 End With
End Function

Book1
	A	B
1	tigerWOODS	tiger WOODS
2	philMICKELSON	phil MICKELSON
3	jordanSPIETH	jordan SPIETH
4	maxHOMA	max HOMA
Sheet1

Cell Formulas
Range	Formula
B1:B4	B1	=jec(A1)

 

[JEC]

Or if you use Office 365

=LET(x,SEQUENCE(LEN(A1),,2),REPLACE(A1,MIN(IFERROR(1/(1/((CODE(MID(A1,x,1))<97)*x)),EXP(99))),0," "))

Or for older versions you can use this as array formula ( close with CTRL + SHIFT + RETURN)

=REPLACE(A1,MIN(IFERROR(1/(1/((CODE(MID(A1,ROW($2:$99),1))<97)*(ROW($2:$99)))),EXP(99))),0," ")

 

[JEC]

And a Power Query solution.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    sp = Table.SplitColumn(Source, "Name", Splitter.SplitTextByCharacterTransition({"a".."z"}, {"A".."Z"}), {"Name", "Name2"}),
    comb = Table.CombineColumns(sp,{"Name", "Name2"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Name")
in
    comb

Book1
	A	B	C
1	Name		Name
2	tigerWOODS		tiger WOODS
3	philMICKELSON		phil MICKELSON
4	jordanSPIETH		jordan SPIETH
5	maxHOMA		max HOMA
Sheet1

 

[MSP]

Or if you use Office 365

=LET(x,SEQUENCE(LEN(A1),,2),REPLACE(A1,MIN(IFERROR(1/(1/((CODE(MID(A1,x,1))<97)*x)),EXP(99))),0," "))

Or for older versions you can use this as array formula ( close with CTRL + SHIFT + RETURN)

=REPLACE(A1,MIN(IFERROR(1/(1/((CODE(MID(A1,ROW($2:$99),1))<97)*(ROW($2:$99)))),EXP(99))),0," ")

Thanks @JEC this did the trick. I am on Office 365.

I'm trying to understand this formula: The Name_value1 part of the formula is SEQUENCE(LEN(A2),,2), which seems to start the formula at the 2nd character instead of the 1st. Is that the case? If so, why is that? Also, are 97 and 99 lower/upper ASCII values?

 

[JEC]

They are lowercase. I started at 2 in case of names starting with capital

 

[Eric W]

Another option:

=LET(a,CHAR(SEQUENCE(26,,65)),REPLACE(A1,MIN(FIND(a,A1&CONCAT(a))),0," "))

 

[MSP]

Another option:

=LET(a,CHAR(SEQUENCE(26,,65)),REPLACE(A1,MIN(FIND(a,A1&CONCAT(a))),0," "))

They are lowercase. I started at 2 in case of names starting with capital

Copy. That makes sense. Thank you both for your help on this.

 

[Fluff]

I am on Office 365.

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)