regex - 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

Answer 1

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