Convert an alphanumeric string into an array of numbers in VBA

Question

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:

[99,1918]

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

You can try using regular expressions, based on the numbers 0-9 and a space (in order to distinguish different numbers). — Dominique
– Dominique, Commented Nov 6, 2018 at 11:10

GSerg · Accepted Answer · 2018-11-06 11:19:42Z

5

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

answered Nov 6, 2018 at 11:19

GSerg

78.8k18 gold badges173 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ron Rosenfeld · Accepted Answer · 2018-11-06 13:07:10Z

Just for kicks, if you have Excel 2016 with the TEXTJOIN function, you could use this array formula:

=TEXTJOIN(",",TRUE,IFERROR(--(MID(SUBSTITUTE(TRIM(A1)," ",REPT(" ",99)),seq_99,99)),""))

where

seq_99 refers to:  =IF(ROW(INDEX($1:$65535,1,1):INDEX($1:$65535,255,1))=1,1,(ROW(INDEX($1:$65535,1,1):INDEX($1:$65535,255,1))-1)*99)

To enter/confirm an array formula, hold down ctrl + shift while hitting enter. If you do this correctly, Excel will place braces {...} around the formula seen in the formula bar.

Or, to show the brackets as I see you did in your code:

=CONCAT("[",TEXTJOIN(",",TRUE,IFERROR(--(MID(SUBSTITUTE(TRIM(A1)," ",REPT(" ",99)),seq_99,99)),"")),"]")

If I were going to use VBA, I'd use something like:

Function StringToIntArray(str As String) As String
    Dim v, w, s As String

v = Split(str)
For Each w In v
    If IsNumeric(w) Then
        s = s & "," & w
    End If
Next w

StringToIntArray = "[" & Mid(s, 2) & "]"

End Function

Vityata · Accepted Answer · 2018-11-06 13:20:43Z

4

If you decide to avoid using RegEx, this is a faster, working solution:

Option Explicit

Sub TestMe()

    Dim stringInput As String
    stringInput = "I am 99 years old and I was born in 1918"
    Debug.Print Join(StringToIntArray(stringInput), " ")

End Sub

Function StringToIntArray(myInput As String) As Variant

    Dim i As Long
    Dim infoArray As Variant
    Dim unitArray As Variant
    Dim result As Variant

    infoArray = Split(myInput)

    For Each unitArray In infoArray
        If IsNumeric(unitArray) Then
            result = result & " " & unitArray
        End If
    Next
    StringToIntArray = Split(Trim(result))

End Function

It splits the string by spaces to an array - infoArray.
For each unit, if the unit is numeric it adds it to a string result.
The string result is parsed to a StringToIntArray through Split() operation.

As probably noted, if there is a . at the end of the stringInput, like this "... in 1918.", the year "1918" would not be taken into account, because it is "1918." and it is not a numeric. Thus, additional "strip" has to be carried out, stripping the dots.

edited Nov 6, 2018 at 13:20

answered Nov 6, 2018 at 11:33

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

5 Comments

GSerg Over a year ago

Same kind stripping has to be done for e.g. commas after numbers inside the string. I am 99, you know.

Vityata Over a year ago

@GSerg - yes, the quick performance comes with a price. For no strip the RegEx is probably the best option. Or loop through every character.

GSerg Over a year ago

Well, running your function (just StringToIntArray, without the outer Join) 100000 times takes 628ms on machine, and running my function 100000 times takes 609ms...

Vityata Over a year ago

@GSerg - this is because of the string concatenation within the function, it is an extremely slow process. In the general case, I would have added to a list, but it was too much information in the answer. If you want to test again, remove this line result = result & " " & unitArray and see the difference.

T.M. Over a year ago

Friendly hint: suggest to return StringToIntArray = Split(Trim(result)) otherwise you get an empty array item first :-)

Gary's Student · Accepted Answer · 2018-11-06 12:56:03Z

Consider:

Public Function StringToIntArray(str As String) As Variant
    Dim temp As String, i As Long, L As Long
    Dim CH As String
    Dim wf As WorksheetFunction
    Set wf = Application.WorksheetFunction

    temp = ""
    L = Len(str)
    For i = 1 To L
        CH = Mid(str, i, 1)
        If CH Like "[0-9]" Then
            temp = temp & CH
        Else
            temp = temp & " "
        End If
    Next i

    StringToIntArray = Split(wf.Trim(temp), " ")

End Function

For example:

Sub MAIN()
    Dim s As String, arr, a, output As String
    s = "qwerty45bgt567cdrew098"

    arr = StringToIntArray(s)
    output = ""

    For Each a In arr
        output = output & a & vbCrLf
    Next a
    MsgBox output

End Sub

The code does not rely on on the [space] character. It will return an array of all numeric sub-strings of an input string.

Collectives™ on Stack Overflow

Convert an alphanumeric string into an array of numbers in VBA

4 Answers 4

Comments

Comments

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related