Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 158
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,934
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 158
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,045
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,045
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,934
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Find Numeric data RobWN Excel Worksheet Functions 16 August 27th 07 12:09 AM
Find and Return Numeric Label based on (Numeric Value) Criterion Sam via OfficeKB.com Excel Worksheet Functions 2 September 18th 06 11:20 PM
Find Numeric Criterion in Column & Return the Numeric Value from Row above Sam via OfficeKB.com Excel Worksheet Functions 6 April 27th 06 02:50 PM
Find numeric value at end of string Barb Reinhardt Excel Worksheet Functions 13 February 5th 06 12:31 AM
MACRO TO FIND SUBSTRING OR SUBTEXT ! jay dean Excel Programming 3 November 20th 04 08:33 PM


All times are GMT +1. The time now is 05:37 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"