Forum Discussion

Rohit_Sonwane's avatar
Rohit_Sonwane
Copper Contributor
Aug 13, 2021
Solved

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 ...
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Aug 13, 2021

    Rohit_Sonwane 

    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")
)
HansVogelaar's avatar
HansVogelaar
MVP
Aug 13, 2021

Rohit_Sonwane 

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.

Resources