Forum Discussion
Looking for a Formula
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.
In 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") )
8 Replies
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))Same with recently introduced functions
=TRIM( CONCAT( MAP( MID( A1, SEQUENCE( LEN( A1 ) ),1 ), LAMBDA(v, IF( EXACT( UPPER(v), v), " " & v, v ) ) ) ) )
In 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") )- Thank you very much, Sir.
Best approach.Rohit_Sonwane , you are welcome
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 FunctionEnter 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.
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)- Thank you Sir
