Count numbers in cell

[samdal]

I have a huge number of cells with varying length of text and varying length of numbers. I want to count the length of the first number in a text string (might be several numbers in one text string. For example, in the string abcd1234def567 I want to count to 4 based on the number 1234 (excluding 567 from the count).

(Ideally I want a formula that returns the number 1234 in a different cell. Both length of text and length of numbers might vary, but I want to return the first string of numbers that occur in a cell).

 

[RickXL]

Hi and welcome to the forum.

You did not actually say whether you wanted a worksheet function or a macro. I have written a macro which is a User Defined Function. You will need to go into the macro editor, insert a new module and paste in this code.

Function RegEx(strToSearch As String) As String

    ' Note: Requires reference to "Microsoft VBScript Regular Expression"
    
    ' Define variables
    Dim objRegExp As RegExp
    Dim objMatches As MatchCollection
    
    ' Create RegEx Object
    Set objRegExp = CreateObject("vbscript.regexp")
    
    ' Set pattern to find any number of digits
    objRegExp.Pattern = "\d+"

    ' Execute the RegEx
    Set objMatches = objRegExp.Execute(strToSearch)
    
    ' Return the first number found
    RegEx = objMatches.Item(0).Value
    
End Function

While you are there you will need to click on the "Tools-->References..." menu item and search for the "Microsoft VBScript Regular Expression" item and select it. This will make the Regular Expression functionality and objects available to you.

Then you should be able to go to the worksheet and put your string, say "abcd1234def567 ", into cell A1. Then if you put:
=REGEX(A1)
into another cell it will return the number 1234.

There is a mountain of stuff on regular expressions on the internet if you ever need to search for something else.

 

[samdal]

Perfect, this did the trick! Thanks!

Hi and welcome to the forum.

You did not actually say whether you wanted a worksheet function or a macro. I have written a macro which is a User Defined Function. You will need to go into the macro editor, insert a new module and paste in this code.

Function RegEx(strToSearch As String) As String

    ' Note: Requires reference to "Microsoft VBScript Regular Expression"
    
    ' Define variables
    Dim objRegExp As RegExp
    Dim objMatches As MatchCollection
    
    ' Create RegEx Object
    Set objRegExp = CreateObject("vbscript.regexp")
    
    ' Set pattern to find any number of digits
    objRegExp.Pattern = "\d+"

    ' Execute the RegEx
    Set objMatches = objRegExp.Execute(strToSearch)
    
    ' Return the first number found
    RegEx = objMatches.Item(0).Value
    
End Function

While you are there you will need to click on the "Tools-->References..." menu item and search for the "Microsoft VBScript Regular Expression" item and select it. This will make the Regular Expression functionality and objects available to you.

Then you should be able to go to the worksheet and put your string, say "abcd1234def567 ", into cell A1. Then if you put:
=REGEX(A1)
into another cell it will return the number 1234.

There is a mountain of stuff on regular expressions on the internet if you ever need to search for something else.

 

[RickXL]

Great news!

Thanks for letting me know.