I need a formula to separate Christian names from surnames when joined together in a name in a text cell e.g. "JohnSmith" needs to become "John Smith"

[Cozkincaud]

HI,

I have a spreadsheet with a column of cells containing names (Christian name and surname). Sometimes there is no space between the Christian name and surname: e.g. "Richard King" appears as "RichardKing". In these cases I need to add a space between the Christian name and the surname. I imagine I could do this with a formula that:

1. finds the second capital letter in the text cell and inserts a space before it (my preferred choice)
2. finds the first string that starts with a lowercase letter that is followed by an uppercase letter and then inserts a space between them.

Unfortunately, I have not been able to find any Excel formulas that do any of the above.

(I could also remove the first capital letter; then find the first capital letter in the text string and place a space before it. I would place the first letters of the Christian names, which are all uppercase, in the cells of another column. I would then add them back to the cells holding the names once I had inserted the required space between the Christian name and the surname. This way I only have to search for the first uppercase letter. I'm assuming it is easier to find the first uppercase letter in a string cell than the nth one.

So I would be most grateful to any forum members who could devise or direct me to any Excel formula that would help me separate my joned Christian names and surnames

The string cells I am working with can contain any of the following types of joined Christian names and surnames:

1. Just one Christian name and surname not separated as in: JohnWilliams
2. Two names with one of the names not having the Christian name separated from the surname as in: RobertJones Thomas Smith
3. Two names with both of the names not having the Christian name separated from the surname as in: HenryPhillips AgnesWithers
4 Several names with some of the names not having the Christian name separated from the surname as in: William Putnam Tod Phillips Adamwalters Tim Thomas ArthurBrown

Note: 1. The first letter of the Christian name and the surname are always capitalized while the rest of the Christian name and surname are always in lower case.
2. The names themselves are always separated by a space.

 

[hagia_sofia]

Hello, if you are using Excel 365 and have access to REGEX functions, you maybe could e.g. try something like:

=REGEXREPLACE(A1,"(?<=[a-z])(?=[A-Z])"," ")

 

[Cozkincaud]

Hello, if you are using Excel 365 and have access to REGEX functions, you maybe could e.g. try something like:

=REGEXREPLACE(A1,"(?<=[a-z])(?=[A-Z])"," ")

Thanks for the suggestion but I'm still using Microsoft Professional 2021 and don't have the Regex, TextBefore and TexAfter functions.

 

[Eric W]

Try:

Book1
	A	B
1	Name	With added spaces
2	John Williams	John Williams
3	JohnWilliams	John Williams
4	RobertJones Thomas Smith	Robert Jones Thomas Smith
5	HenryPhillips AgnesWithers	Henry Phillips Agnes Withers
6	William Putnam Tod Phillips Adamwalters Tim Thomas ArthurBrown	William Putnam Tod Phillips Adamwalters Tim Thomas Arthur Brown
Sheet10

Cell Formulas
Range	Formula
B2:B6	B2	=LET(n,A2,s,SEQUENCE(LEN(n)),a,MID(n,s,1),CONCAT(IF(s=1,a,(IF((INDEX(a,s-1)<>" ")*((CODE(a)>=65)*(CODE(a)<=90))," "&a,a)))))

This was tested on Excel 2021. The formula basically looks for capitalized letters with no previous space, and adds the space. Note on B6 how Adamwalters was not split, since walters did not start with a capital letter.

 

[Peter_SSs]

I'm still using Microsoft Professional 2021

I suggest that you update your forum profile (click your user name at the top right of the forum, then ‘Account details’) 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’)

 

[Peter_SSs]

Another possible option

25 02 22.xlsm
	A	B
1	Data	Result
2	John Williams	John Williams
3	JohnWilliams	John Williams
4	RobertJones Thomas Smith	Robert Jones Thomas Smith
5	HenryPhillips AgnesWithers	Henry Phillips Agnes Withers
6	William Putnam Tod Phillips Adamwalters Tim Thomas ArthurBrown	William Putnam Tod Phillips Adamwalters Tim Thomas Arthur Brown
Names

Cell Formulas
Range	Formula
B2:B6	B2	=LET(c,MID(A2,SEQUENCE(LEN(A2)),1),TRIM(CONCAT(IF(EXACT(c,UPPER(c))," ","")&c)))