vba / udf to add space

[Endeavouring]

Hi

I need to add a space between text and numbers in any cell in a single column. Either the numeric or alpha could be first and each cell could contain any combination of alpha/numeric. Such as

A12
A12xyz
ABC123xyz
abc1234567890xyz9876543

Which would need changing to
A 12
A 12 xyz
ABC 123 xyz
abc 1234567890 xyz 9876543

If possible I am trying to make it either a VBA Sub or UDF

Any help would be appreciated as I've been batting this around with no success.

 

[Sektor]

UDF

Function AddSpace(Str As String) As String

    Application.Volatile
    
    Dim s As String
    Dim i As Integer
    Dim nextChar As String, currChar As String

    If Len(Str) < 2 Then
        AddSpace = Str
        Exit Function
    End If
    
    For i = 1 To Len(Str)
        
        nextChar = Mid(Str, i + 1, 1)
        currChar = Mid(Str, i, 1)
        
        If (IsNumeric(currChar) And (Not IsNumeric(nextChar))) Or _
           (Not IsNumeric(currChar) And IsNumeric(nextChar)) Then
            s = s & currChar & " "
        Else
            s = s & currChar
        End If
    Next
   
    AddSpace = s

End Function

 

[Endeavouring]

Hi Sektor

Works great, but inserts a space after the last character if it is numeric !

 

[Peter_SSs]

If you only have letters and digits in the cell, as in your samples, then try this UDF.

<font face=Courier New><br><SPAN style="color:#00007F">Function</SPAN> AddSpaces(s <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Static</SPAN> RegEx <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN><br>    <br>    <SPAN style="color:#00007F">If</SPAN> RegEx <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> RegEx = CreateObject("VBScript.RegExp")<br>        <SPAN style="color:#00007F">With</SPAN> RegEx<br>            .Global = <SPAN style="color:#00007F">True</SPAN><br>            .Ignorecase = <SPAN style="color:#00007F">True</SPAN><br>            .Pattern = "((\D)(?=\d)|(\d)(?=\D))"<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    AddSpaces = RegEx.Replace(s, "$1 ")<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN><br><br></FONT>

Excel Workbook
	A	B
1	A12	A 12
2	A12xyz	A 12 xyz
3	ABC123xyz	ABC 123 xyz
4	abc1234567890xyz9876543	abc 1234567890 xyz 9876543
Add Spaces

 

[Peter_SSs]

My previous code will add a space between any digit and any non-digit.
So "1_2" would become "1 _ 2"

If you could have other characters like that in the cells and only want a space between letters and digits, try replacing the pattern line with

.Pattern = "(([a-z])(?=[0-9])|([0-9])(?=[a-z]))"

 

[Endeavouring]

Cheers SSs

Never could get to grips with Regexp but can see what it does and it works great.
Thanks

 

[Endeavouring]

Hi Peter

Not certain quite what I'm expecting to see, but trying the 2 I appear to get the same result !. When I added an _ to second string between the 12 of rows 1&2 ?

 

[Peter_SSs]

That's probably because the code is not geting to the Pattern line. To make the code faster if you have a lot of data, it is structured so that RegEx is only Set once, as is Global, IgnoreCase and Pattern.

Probably the easiest way while you are experimenting with different patterns (and small amounts of data) is to move the Pattern line to just after the "End If" line.

If you are not using the function in a lot of cells on your worksheet, the Pattern line could stay outside the IF ... End If block.

 

[Sektor]

It was needed to trim result string:

Function AddSpace(Str As String) As String

    Application.Volatile
    
    Dim s As String
    Dim i As Integer
    Dim nextChar As String, currChar As String

    If Len(Str) < 2 Then
        AddSpace = Str
        Exit Function
    End If
    
    For i = 1 To Len(Str)
        
        nextChar = Mid(Str, i + 1, 1)
        currChar = Mid(Str, i, 1)
        
        If (IsNumeric(currChar) And (Not IsNumeric(nextChar))) Or _
           (Not IsNumeric(currChar) And IsNumeric(nextChar)) Then
            s = s & currChar & " "
        Else
            s = s & currChar
        End If
    Next
   
    AddSpace = [B]Trim[/B](s)

End Function

 

[Endeavouring]

Thank you gentlemen