Extracting a numbers from a text string
On Mon, 26 Feb 2007 14:42:15 -0800, IPerlovsky
wrote:
Ron,
Thank you for the tip. I will study these functions to obtain a more
thorough understanding of the application's potential.
You're welcome. It works on all of the examples you've given.
=REGEX.MID(A1,"(?<=\s)-?\d+(?=\s)",-1)
The Regular Expression is the section within the double quote marks.
Translation:
(?<=\s)
Look for a space (but don't extract it)
followed by
-?
a minus sign which is optional
followed by
\d+
a string of digits
followed by
(?=\s)
a space (but don't extract it).
The -1 at the end says make this the very last sequence in the string that
meets this description.
The following formula returns the same values, but, since we TRIM the spaces
instead of altering the regular expression so as not to return the spaces,
might be easier to understand:
=TRIM(REGEX.MID(A1,"\s-?\d+\s",-1))
The pattern that matches the regex would be:
<space<optional minus sign<series of digits<space
\s -? \d+ \s
--ron
|