Here is a regex-based function that should do the trick. Paste it into a standard module that you've inserted from the VBA editor menu. You can access the editor by clicking Visual Basic on the Developer tab of the ribbon. Then choose Insert -> Standard Module on the menu.
You will also need to set a reference to the
Microsoft VBScript Regular Expressions 5.5 library. To do that, click Tools -> References on the editor menu. Scroll down to find the listing and check-mark it to set the reference.
Regex experts would want to improve upon the pattern I used. It inserts extra spaces into some strings, which I successfully got rid of using the worksheet
Function ReNSpace(text As String)
Dim regex As New RegExp
regex.Pattern = "(?:(\D+)?(\d+))|(?:(\D+)(\d+)?)"
regex.Global = True
regex.IgnoreCase = True
' Test if a match is found
If regex.Test(text) = True Then
ReNSpace = WorksheetFunction.Trim(regex.Replace(text, "$1 $2 $3 $4"))
ReNSpace = CVErr(xlErrValue)
Set regex = Nothing