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.

 

[Peter_SSs]

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)
        
        [COLOR="Blue"]If (IsNumeric(currChar) And (Not IsNumeric(nextChar))) Or _
           (Not IsNumeric(currChar) And IsNumeric(nextChar)) Then[/COLOR]
            s = s & currChar & " "
        Else
            s = s & currChar
        End If
    Next
   
    AddSpace = [B]Trim[/B](s)

End Function

Just an observation on this code. I think the blue line could be condensed to

If IsNumeric(currChar) + IsNumeric(nextChar) = -1 Then

 

[Sektor]

Peter_SSs,
Yes, it's more compact. Just had no time to optimize it.

 

[Endeavouring]

Gentlemen, your help is overwhelming.
Many thanks