Aug 13 2021 08:32 AM
Respected Sir/Madam
I want to extract first name, middle name, and last name with a space.
But the main problem is there is no delimiter in my data. Only delimiter can be the uppercase word. My data look like this "RohitSanjaySonwane" and I want this "Rohit Sanjay Sonwane" with a space. I want a formula to solve this problem. I am sharing my file.
Aug 13 2021 09:53 AM
@Rohit_Sonwane Assuming you have Excel365 here is a formula that will work:
=LET(in,$A1,l,LEN(in),
seq,SEQUENCE(,l),
letter,MID(in,seq,1),
addSpace,IF((CODE(letter)>=CODE("A"))*(CODE(letter)<=CODE("Z"))," "&letter,letter),
out,TRIM(CONCAT(addSpace)),
out)
Aug 13 2021 10:03 AM
Create the following custom function in a module in the Visual Basic Editor:
Function ExpandName(s As String) As String
Dim i As Long
Dim r As String
r = Left(s, 1)
For i = 2 To Len(s)
If Mid(s, i, 1) <= "Z" Then
r = r & " "
End If
r = r & Mid(s, i, 1)
Next i
ExpandName = r
End Function
Enter the following formula in B2:
=ExpandName(A2)
Fill down.
Save the workbook as a macro-enabled workbook (.xlsm).
You'll have to allow macros when you open the workbook.
See the attached version.
Aug 13 2021 10:18 AM
SolutionIn straightforward legacy style
=TRIM(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(A1,
"A"," A"),
"B"," B"),
"C"," C"),
"D"," D"),
"E"," E"),
"F"," F"),
"G"," G"),
"H"," H"),
"I"," I"),
"J"," J"),
"K"," K"),
"L"," L"),
"M"," M"),
"N"," N"),
"O"," O"),
"P"," P"),
"Q"," Q"),
"R"," R"),
"S"," S"),
"T"," T"),
"U"," U"),
"V"," V"),
"W"," W"),
"X"," X"),
"Y"," Y"),
"Z"," Z")
)
Aug 13 2021 12:28 PM
Another approach:
=LET(
prop,PROPER(A1),
seq_1,SEQUENCE(LEN(A1)),
letters1,MID(A1,seq_1,1),
seq_2,SEQUENCE(LEN(prop)),
letters2,MID(prop,seq_2,1),
ex,EXACT(letters1,letters2),
CONCAT(IF(ex,""," ")&letters1))
Aug 13 2021 02:45 PM
Same with recently introduced functions
=TRIM( CONCAT( MAP( MID( A1, SEQUENCE( LEN( A1 ) ),1 ), LAMBDA(v, IF( EXACT( UPPER(v), v), " " & v, v ) ) ) ) )
Aug 13 2021 10:18 AM
SolutionIn straightforward legacy style
=TRIM(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(A1,
"A"," A"),
"B"," B"),
"C"," C"),
"D"," D"),
"E"," E"),
"F"," F"),
"G"," G"),
"H"," H"),
"I"," I"),
"J"," J"),
"K"," K"),
"L"," L"),
"M"," M"),
"N"," N"),
"O"," O"),
"P"," P"),
"Q"," Q"),
"R"," R"),
"S"," S"),
"T"," T"),
"U"," U"),
"V"," V"),
"W"," W"),
"X"," X"),
"Y"," Y"),
"Z"," Z")
)