# 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
###### Who is Participating?

Commented:
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
``````
Space-text-and-numbers.xlsm
0

Author 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.