Count numbers in cell

samdal

New Member
Joined
Sep 17, 2014
Messages
2
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).
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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.

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.
 
Upvote 0
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.

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.
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top