formula to add space between alpha numeric string

hello

i need help with the formula.  

i have mixed alpha numberic string in many cells in column A and i want a formula in column B that adds space between alpha and numbers  either way. for example if   an text with number is attached like this  James54125 Nick5411 then in ciolumn B result James 54125 Nick 5411 or  the other way  5412211James 74555555Nicolay  result in column B  5412211 James 74555555 Nicolay  

please see attached file.  i found the formula =TRIM(LEFT(A2, MATCH(TRUE, MID(A2, {1,2,3,4,5,6,7}, 1)>"9", 0) - 1) & " " & MID(A2, MATCH(TRUE, MID(A2, {1,2,3,4,5,6,7}, 1)>"9", 0), 100) )

but it does not work, please see attached file.
Book2.xlsx
LVL 6
FloraAsked:
Who is Participating?
 
Ejgil HedegaardCommented:
I think that is too complicated to make as a formula, with text and numbers mixed several times.
Try this UDF function (VBA), see sheet.

Function SpaceTextAndNumbers(Txt As String) As String
    Dim i As Integer, TextType As String
    If Asc(Mid(Txt, 1, 1)) >= 48 And Asc(Mid(Txt, 1, 1)) <= 57 Or Asc(Mid(Txt, 1, 1)) = 46 Then
        TextType = "Nbr"
    Else
        TextType = "Txt"
    End If
    i = 2
    Do
        If Mid(Txt, i, 1) <> " " Then
            If Asc(Mid(Txt, i, 1)) >= 48 And Asc(Mid(Txt, i, 1)) <= 57 Or Asc(Mid(Txt, i, 1)) = 46 Then
                If TextType = "Txt" Then
                    TextType = "Nbr"
                    Txt = Left(Txt, i - 1) & " " & Right(Txt, Len(Txt) - i + 1)
                End If
            Else
                If TextType = "Nbr" Then
                    TextType = "Txt"
                    Txt = Left(Txt, i - 1) & " " & Right(Txt, Len(Txt) - i + 1)
                End If
            End If
        End If
        i = i + 1
    Loop Until i > Len(Txt)
    SpaceTextAndNumbers = Application.WorksheetFunction.Trim(Txt)
End Function

Open in new window

Space-text-and-numbers.xlsm
0
 
FloraAuthor Commented:
Ejgil,

you are brilliant
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.