Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find the first numeric substring
Suppose I have the following string:
"The street is 1501 S. Oak Lane, Apt #228" I need code that would extract the first numeric substring (starting from the LEFT). Therefore, I would need to extract "1501 from this string. Or, I might have a string that looks like this: "cat. dog mouse 12 8 112 house tree" For that string, I would need to extract the "12" only, because it is the first numeric substring or token. Does anyone have any code samples that can extract the substring that I describe above?? Please note that any number of whitespace (space or tab) characters may separate the string elements. Thank you! Robert Crandal |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find the first numeric substring
You didn't tell us whether the numbers you are trying to find could possibly
be floating point values or not. The following will work for whole numbers or floating point numbers where the decimal point is a "dot" ... Dim X As Long, FirstNumber As Double, Text As String Text = "cat. dog mouse 12 8 112 house tree" For X = 1 To Len(Text) If IsNumeric(Mid(Text, X, 1)) Then FirstNumber = Val(Mid(Replace(Text, " ", "Z"), X)) Exit For End If Next If your decimal point is a "comma", then the following code should work for you... Dim X As Long, FirstNumber As Double, Text As String Text = "cat. dog mouse 12 8 112 house tree" For X = 1 To Len(Text) If IsNumeric(Mid(Text, X, 1)) Then FirstNumber = Val(Mid(Replace(Replace(Text, ",", "."), " ", "Z"), X)) Exit For End If Next Rick Rothstein (MVP - Excel) Robert Crandal" wrote in message ... Suppose I have the following string: "The street is 1501 S. Oak Lane, Apt #228" I need code that would extract the first numeric substring (starting from the LEFT). Therefore, I would need to extract "1501 from this string. Or, I might have a string that looks like this: "cat. dog mouse 12 8 112 house tree" For that string, I would need to extract the "12" only, because it is the first numeric substring or token. Does anyone have any code samples that can extract the substring that I describe above?? Please note that any number of whitespace (space or tab) characters may separate the string elements. Thank you! Robert Crandal |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find the first numeric substring
Ooops, my mistake about that. Um, all my data samples
ONLY contain whole numbers or integer substrings, so there will never be any floating point numbers. To be even more precise, I am only looking for an ALL numeric substring that has at least one whitespace character on the left and at least one whitespace character on the right. So, for example: 1) "cat dog11 23 tree carrot" - should return "23" 2) "rock paper scissors 12b 100 pencil" - should return "100" I should have been more specific earlier, but I'm just now realizing exactly what I need. Also, you can safely assume that all my data will contain at least one number substring that matches the above criteria or specs; there will never be any data that is missing numbers padded by whitespace characters. Okay, I'll play with you code now and see if it still works for my requirements. Thanks Rick "Rick Rothstein" wrote in message ... You didn't tell us whether the numbers you are trying to find could possibly be floating point values or not. The following will work for whole numbers or floating point numbers where the decimal point is a "dot" ... Dim X As Long, FirstNumber As Double, Text As String Text = "cat. dog mouse 12 8 112 house tree" For X = 1 To Len(Text) If IsNumeric(Mid(Text, X, 1)) Then FirstNumber = Val(Mid(Replace(Text, " ", "Z"), X)) Exit For End If Next If your decimal point is a "comma", then the following code should work for you... Dim X As Long, FirstNumber As Double, Text As String Text = "cat. dog mouse 12 8 112 house tree" For X = 1 To Len(Text) If IsNumeric(Mid(Text, X, 1)) Then FirstNumber = Val(Mid(Replace(Replace(Text, ",", "."), " ", "Z"), X)) Exit For End If Next Rick Rothstein (MVP - Excel) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find the first numeric substring
On Tue, 12 Apr 2011 01:23:40 -0700, "Robert Crandal" wrote:
Suppose I have the following string: "The street is 1501 S. Oak Lane, Apt #228" I need code that would extract the first numeric substring (starting from the LEFT). Therefore, I would need to extract "1501 from this string. Or, I might have a string that looks like this: "cat. dog mouse 12 8 112 house tree" For that string, I would need to extract the "12" only, because it is the first numeric substring or token. Does anyone have any code samples that can extract the substring that I describe above?? Please note that any number of whitespace (space or tab) characters may separate the string elements. Thank you! Robert Crandal For a worksheet formula, you could use: =LOOKUP(1E+307,--MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&"1,2,3,4, 5,6,7,8,9,0")),ROW($1:$99))) which will return an error if there are no digits in the string. For VBA code, one way with regular expressions, and, for this exercise, assuming that all of the values are unsigned digit strings, and this returns the value as a string, and returns nothing if there are no digits: (returning the result as a string allows retention of leading zero's) =================== Option Explicit Function ExtrDigits(s As String) As String Dim re As Object, mc As Object Const sPat As String = "\d+" Set re = CreateObject("vbscript.regexp") re.Global = False re.Pattern = sPat If re.test(s) Then Set mc = re.Execute(s) ExtrDigits = mc(0) End If End Function ========================== If you need to also handle floating point numbers, signed numbers or fractions, the Pattern can be changed to accomodate. If you need to return more than the first digit, the code can be altered easily to handle this. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find the first numeric substring
On Tue, 12 Apr 2011 02:16:39 -0700, "Robert Crandal" wrote:
Ooops, my mistake about that. Um, all my data samples ONLY contain whole numbers or integer substrings, so there will never be any floating point numbers. To be even more precise, I am only looking for an ALL numeric substring that has at least one whitespace character on the left and at least one whitespace character on the right. So, for example: 1) "cat dog11 23 tree carrot" - should return "23" 2) "rock paper scissors 12b 100 pencil" - should return "100" I should have been more specific earlier, but I'm just now realizing exactly what I need. Also, you can safely assume that all my data will contain at least one number substring that matches the above criteria or specs; there will never be any data that is missing numbers padded by whitespace characters. Okay, I'll play with you code now and see if it still works for my requirements. Thanks Rick Given those parameters, this modification of my original code should work: ============================ Option Explicit Function ExtrDigits(s As String) As String Dim re As Object, mc As Object Const sPat As String = "\s(\d+)\s" Set re = CreateObject("vbscript.regexp") re.Global = False re.Pattern = sPat If re.test(s) Then Set mc = re.Execute(s) ExtrDigits = mc(0).submatches(0) End If End Function ========================== |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find the first numeric substring
Ooops, my mistake about that. Um, all my data
samples ONLY contain whole numbers or integer substrings, so there will never be any floating point numbers. To be even more precise, I am only looking for an ALL numeric substring that has at least one whitespace character on the left and at least one whitespace character on the right. Hmm! That changes everything. Okay, here is a function that will do this (it can be used as a UDF, user defined function, if needed)... Function GetNumber(ByVal Text As String) As Double Dim X As Long, Temp As String Text = Replace(Text, Chr(160), " ") For X = 1 To Len(Text) Temp = Mid(Text, X) If Temp Like " #*" Then Temp = Left(LTrim(Temp), InStr(LTrim(Temp), " ") - 1) If Not Temp Like "*[!0-9]*" Then Exit For End If Next GetNumber = Temp End Function Rick Rothstein (MVP - Excel) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find Numeric data | Excel Worksheet Functions | |||
Find and Return Numeric Label based on (Numeric Value) Criterion | Excel Worksheet Functions | |||
Find Numeric Criterion in Column & Return the Numeric Value from Row above | Excel Worksheet Functions | |||
Find numeric value at end of string | Excel Worksheet Functions | |||
MACRO TO FIND SUBSTRING OR SUBTEXT ! | Excel Programming |