Through my work, and copying others, I have cobbled together a Excel VBA Sub that separates a long sting with groups of (text groups) and (number groups) into a replacement string with spaces in between each seperate group; as per this example: • “123abc12aedsw2345der” • …Apply selection Sub() then becomes: • “123 abc 12 aedsw 2345 der” It converts the string in its original cell as per the “selection”, so I am currently left with the altered data in is original cell PROBLEM: I would like to change this into a FUNCTION where the transformed data would appear in the Function cell and leave the original cell intact. I have done hundreds of these but I cannot seem to get this to work as an independent FUNCTION. Below the finished and working Sub Routine I am trying to convert to an independent function to call from anywhere on the worksheet:
Sub SplitTextNumbersSelection()
Dim c As Range
'********** Inserts Space Before Number Groups ******************************
For n = 1 To 10
For Each c In Selection
c = InsertSpace(c.Text)
Next
Next n
'****************Inserts Space Before Letter Groups ***********************
For n = 1 To 10
For Each c In Selection
c = InsertSpace2(c.Text)
Next
Next n
'****************************************
End Sub
Function InsertSpace(str As String) As String
With CreateObject("vbscript.regexp")
.Pattern = "([a-z])(\d)"
'.Pattern = "(\d)([a-z])"
InsertSpace = .Replace(str, "$1 $2")
End With
End Function
Function InsertSpace2(str As String) As String
With CreateObject("vbscript.regexp")
'.Pattern = "([a-z])(\d)"
.Pattern = "(\d)([a-z])"
InsertSpace2 = .Replace(str, "$1 $2")
End With
End Function
CodePudding user response:
Bit simpler:
Function PrepString(v As String)
Dim rv As String, i As Long, c1, c2
For i = 1 To Len(v) - 1
c1 = Mid(v, i, 1)
c2 = Mid(v, i 1, 1)
If (c1 Like "[a-z]" And c2 Like "[0-9]") Or _
(c2 Like "[a-z]" And c1 Like "[0-9]") Then
rv = rv & c1 & " "
Else
rv = rv & c1
End If
Next i
PrepString = rv & Right(v, 1)
End Function