Excel UDF for capturing numbers within characters

Question

I have a variable text field sitting in cell A1 which contains the following:

Text;#Number;#Text;#Number

This format can keep repeating, but the pattern is always Text;#Number.
The numbers can vary from 1 digit to n digits (limit 7)

Example:

Original Value

MyName;#123;#YourName;#3456;#HisName;#78

Required value:

123, 3456, 78

The field is too variable for excel formulas from my understanding.

I tried using regexp but I am a beginner when it comes to coding. if you can break down the code with some explanation text, it would be much appreciated.

I have tried some of the suggestions below and they work perfectly. One more question.

Now that I can split the numbers from the text, is there any way to utilize the code below and add another layer, where we split the numbers into x cells.

For example: once we run the function, if we get 1234, 567 in the same cell, the function would put 1234 in cell B2, and 567 in cell C2. This would keep updating all cells in the same row until the string has exhausted all of the numbers that are retrieved from the function.

Thanks

What part of **Example 2** in [this](http://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops/22542835#22542835) didn't you understand? — , May 02 '16 at 21:48
Split on #, iterate over the result, checking if they are numbers — John Coleman, May 02 '16 at 21:49
I have tried some of the suggestions below and they work perfectly. One more question. Now that I can split the numbers from the text, is there any way to utilize the code below and add another layer, where we split the numbers into x cells. For example: once we run the function, if we get 1234, 567 in the same cell, the function would put 1234 in cell B2, and 567 in cell C2. This would keep updating all cells in the same row until the string has exhausted all of the numbers that are retrieved from the function. — Ray, May 03 '16 at 12:58

Gary's Student · Answer 1 · 2016-05-02T22:51:40.027

2

This is the John Coleman's suggested method:

Public Function GetTheNumbers(st As String) As String
    ary = Split(st, ";#")
    GetTheNumbers = ""

    For Each a In ary
        If IsNumeric(a) Then
            If GetTheNumbers = "" Then
                GetTheNumbers = a
            Else
                GetTheNumbers = GetTheNumbers & ", " & a
            End If
        End If
    Next a
End Function

edited May 02 '16 at 22:51

answered May 02 '16 at 21:57

Gary's Student

91,920
8
47
75

score 2 · Answer 2 · answered May 02 '16 at 23:17

If the pattern is fixed, and the location of the numbers never changes, you can assume the numbers will be located in the even places in the string. This means that in the array result of a split on the source string, you can use the odd indexes of the resulting array. For example in this string "Text;#Number;#Text;#Number" array indexes 1, 3 would be the numbers ("Text(0);#Number(1);#Text(2);#Number(3)"). I think this method is easier and safer to use if the pattern is indeed fixed, as it avoids the need to verify data types.

Public Function GetNums(src As String) As String
    Dim arr
    Dim i As Integer
    Dim result As String
    arr = Split(src, ";#") ' Split the string to an array.
    result = ""
    For i = 1 To UBound(arr) Step 2 ' Loop through the array, starting with the second item, and skipping one item (using Step 2).
        result = result & arr(i) & ", "
    Next
    If Len(result) > 2 Then
        GetNums = Left(result, Len(result) - 2) ' Remove the extra ", " at the end of the the result string.
    Else
        GetNums = ""
    End If
End Function

score 2 · Answer 3 · answered May 02 '16 at 23:43

^{The numbers can vary from 1 digit to n digits (limit 7)}

None of the other responses seems to take the provided parameters into consideration so I kludged together a true regex solution.

Option Explicit
Option Base 0    '<~~this is the default but I've included it because it has to be 0

Function numsOnly(str As String, _
                  Optional delim As String = ", ")
    Dim n As Long, nums() As Variant
    Static rgx As Object, cmat As Object

    'with rgx as static, it only has to be created once; beneficial when filling a long column with this UDF
    If rgx Is Nothing Then
        Set rgx = CreateObject("VBScript.RegExp")
    End If
    numsOnly = vbNullString

    With rgx
        .Global = True
        .MultiLine = False
        .Pattern = "[0-9]{1,7}"
        If .Test(str) Then
            Set cmat = .Execute(str)
            'resize the nums array to accept the matches
            ReDim nums(cmat.Count - 1)
            'populate the nums array with the matches
            For n = LBound(nums) To UBound(nums)
                nums(n) = cmat.Item(n)
            Next n
            'convert the nums array to a delimited string
            numsOnly = Join(nums, delim)
        End If
    End With
End Function

[regex] specific questions can likely be answered [here](http://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops/22542835#22542835). — , May 02 '16 at 23:45
Thank you for the response. This works perfectly. I have to brush up on my regex skills. — Ray, May 03 '16 at 12:51

score 1 · Answer 4 · answered May 03 '16 at 03:36

Regexp option that uses Replace

Sub Test()
Debug.Print StrOut("MyName;#123;#YourName;#3456;#HisName;#78")
End Sub

function

Option Explicit
Function StrOut(strIn As String) As String
Dim objRegex As Object
Set objRegex = CreateObject("vbscript.regexp")
With objRegex
    .Pattern = "(^|.+?)(\d{1,7})"
    .Global = True
    If .Test(strIn) Then
        StrOut = .Replace(strIn, "$2, ")
        StrOut = Left$(StrOut, Len(StrOut) - 2)
    Else
        StrOut = "Nothing"
    End If
End With
End Function

This works as well. Thanks for the response. Would you mind providing a breakdown of how you were able to split the numbers out using this method if you can. — Ray, May 03 '16 at 12:55

Excel UDF for capturing numbers within characters

4 Answers4

Linked

Related