Convert an alphanumeric string into an array of numbers in VBA

  • A+

I've been trying to convert a cell in VBA which is like this:

I am 99 years old and I was born in 1918.

Into an array that would contain and display on the cell as:


Here is my code:

Sub ConvertColumnToArray(Length As Integer)     Dim i As Integer, infoArray As Variant     Set Column = Sheets("Short Desc")     For i = 2 To Length         infoArray = StringToIntArray(Column.Range("A" & i))         Column.Range("A" & i) = "[" & infoArray(0) & "," & infoArray(1) & "]"     End For End Sub  Function StringToIntArray(str As String) As Variant     'the code to add End Function 


Function StringToIntArray(str As String) As Variant   Static regex As Object 'VBScript_RegExp_55.RegExp    If regex Is Nothing Then     Set regex = CreateObject("VBScript.RegExp")  ' New VBScript_RegExp_55.RegExp     regex.Global = True     regex.Pattern = "/d+"   End If    Dim matches As Object ' MatchCollection   Set matches = regex.Execute(str)    If matches.Count > 0 Then     Dim result() As Long     ReDim result(0 To matches.Count - 1)      Dim i As Long     For i = 0 To matches.Count - 1       result(i) = CLng(matches(i).Value)     Next      StringToIntArray = result   End If  End Function 


:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: