separate alphanumeric value with a space

[agid]

Hi every one!

I am looking for a formula or VBA code in Excel to separate alphanumeric value with a space

eg:

asd123df34a ----to----> asd 123 df 34 a

123abc5 ----to----> 123 abc 5
​

Thanks in advance!

Awaiting a reply

 

[Andrew Poulsom]

Try:

Function AddSpaces(Txt As String)
    Dim i As Integer
    AddSpaces = Left(Txt, 1)
    For i = 2 To Len(Txt)
        If IsNumeric(Mid(Txt, i, 1)) Then
            If Not IsNumeric(Mid(Txt, i - 1, 1)) Then
                AddSpaces = AddSpaces & " " & Mid(Txt, i, 1)
            Else
                AddSpaces = AddSpaces & Mid(Txt, i, 1)
            End If
            If Not IsNumeric(Mid(Txt, i + 1, 1)) Then
                AddSpaces = AddSpaces & " "
            End If
        Else
            AddSpaces = AddSpaces & Mid(Txt, i, 1)
        End If
    Next i
End Function

Use it on a worksheet like:

=AddSpaces(A1)

 

[doofusboy]

Assuming the target cell is "A1", this should work:

Dim iCTR As Integer
Dim previousCharacter As Boolean
Dim newValue As String
    For iCTR = 1 To Len(Range("A1").Value)
        If iCTR = 1 Then
            previousCharacter = IsNumeric(Mid(Range("A1").Value, iCTR, 1))
            newValue = Mid(Range("A1").Value, iCTR, 1)
        End If
        If iCTR > 1 Then
            If IsNumeric(Mid(Range("A1").Value, iCTR, 1)) = previousCharacter Then
                newValue = newValue & Mid(Range("A1").Value, iCTR, 1)
                previousCharacter = IsNumeric(Mid(Range("A1").Value, iCTR, 1))
            Else
                newValue = newValue & " " & Mid(Range("A1").Value, iCTR, 1)
                previousCharacter = IsNumeric(Mid(Range("A1").Value, iCTR, 1))
            End If
        End If
    Next iCTR
    Range("A1").Value = newValue

 

[Richard Schollar]

Here's one using Regexp:

Function Ins_Space(s As String)
With CreateObject("vbscript.regexp")
    .Global = True
    .Pattern = "(\d+)"
    Ins_Space = Trim(.Replace(s, " $1 "))
End With
End Function

Use like:

=Ins_Space(A1)

 

[agid]

THANKS a lot!!!!

You people are great!!!!!

TONS of THANKS!!!!