Re: Extracting a numbers from a text string
- From: Ron Rosenfeld <ronrosenfeld@xxxxxxxxxx>
- Date: Mon, 26 Feb 2007 21:38:31 -0500
On Mon, 26 Feb 2007 14:42:15 -0800, IPerlovsky <iperlovsky@xxxxxxxxxxxxxxx>
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
.
- References:
- Re: Extracting a numbers from a text string
- From: Ron Rosenfeld
- Re: Extracting a numbers from a text string
- Prev by Date: Re: Retrieve the Nth element in a dataset
- Next by Date: Re: Retrieve the Nth element in a dataset
- Previous by thread: Re: Extracting a numbers from a text string
- Next by thread: Re: translating lotus 123 formulas into excel
- Index(es):
Relevant Pages
|
