Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Retrieve numbers that meet criteria
I have a column with text. I'd like to retrieve all of the 3 and 4
digit numbers from it and put it into a new column/columns. For example: The 5 kids ate 348 hot dogs - 348 3248 kids ate 4000burgers - 3248, 4000 Is there a way of doing this using Excel commands or VBA? Jeff |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Retrieve numbers that meet criteria
On Sat, 30 Jul 2011 21:37:10 -0700 (PDT), jeffrey wrote:
I have a column with text. I'd like to retrieve all of the 3 and 4 digit numbers from it and put it into a new column/columns. For example: The 5 kids ate 348 hot dogs - 348 3248 kids ate 4000burgers - 3248, 4000 Is there a way of doing this using Excel commands or VBA? Jeff This can be done simply with a VBA User Defined Function using Regular Expressions: To enter this User Defined Function (UDF), <alt-F11 opens the Visual Basic Editor. Ensure your project is highlighted in the Project Explorer window. Then, from the top menu, select Insert/Module and paste the code below into the window that opens. To use this User Defined Function (UDF), with your data in column A, enter a formula like =GetNums($A1,COLUMNS($A:A)) in some cell, and fill right for as many cells as there might be number in the sentence. The "COLUMNS(.." argument will auto-increment to give a value corresponding to the Index in the function, so as to pull out the desired number from the string. ==================================== Option Explicit Function GetNums(s As String, Optional I As Long = 1) As Variant Dim re As Object, mc As Object Set re = CreateObject("vbscript.regexp") With re .Global = True .MultiLine = True .Pattern = "(?:^|\D)(\d{3,4})(?=\D|$)" End With Set mc = re.Execute(s) If mc.Count = I Then GetNums = mc(I - 1) Else GetNums = "" End If End Function ================================== |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need to meet criteria | Excel Programming | |||
EXCEL - Meet 2 criteria, then find next case of third criteria | Excel Worksheet Functions | |||
add numbers if they meet criteria...? | Excel Worksheet Functions | |||
how do I count the numbers of row that meet 2 criteria | Excel Worksheet Functions | |||
Formula that only adds numbers that meet specific criteria | Excel Discussion (Misc queries) |