what are the regular expression special characters while searching

  • Thread starter Thread starter Howdy
  • Start date Start date
H

Howdy

Search looking for numbers embedded in text, can you use [0-9]+

My address is 1235 Main Street
Your address is 34 Elm Street
 
This find position of first digit
Gives erroneous result is no digit present. No time now to work on it
=MIN(FIND({"1","2","3","4","5","6","7","8","9"},A1&"123456789"))
best wishes
 
Search looking for numbers embedded in text, can you use [0-9]+

My address is 1235 Main Street
Your address is 34 Elm Street
Click to expand...

Your question is not clear.

If you want to extract the first substring of one or more consecutive digits in
a string, you could use:

=LOOKUP(2,1/MID(A1,MIN(SEARCH({1,2,3,4,5,6,7,8,9,0},A1&{"1,2,3,4,5,6,7,8,9,0"})),ROW(INDIRECT("1:99"))),
MID(A1,MIN(SEARCH({1,2,3,4,5,6,7,8,9,0},A1&{"1,2,3,4,5,6,7,8,9,0"})),ROW(INDIRECT("1:99"))))

"99" is just some value that is longer than the longest string.

This will return a #NA error if there is no number in the string.

For SEARCH worksheet function acceptable wild cards, look at HELP for that
function. It seems pretty clear to me:

=====================
You can use the wildcard characters — the question mark (?) and asterisk (*) —
in the find_text argument. A question mark matches any single character; an
asterisk matches any sequence of characters. If you want to find an actual
question mark or asterisk, type a tilde (~) before the character.
=====================

For wildcard characters in the FIND and REPLACE dialog box, see HELP for that
method (they are the same, if I recall correctly).

There is no native support in Excel for regular expressions.

If you want to use regular expressions in Excel, you will have to use VBA to
write the appropriate UDF's, or download and install Longre's free morefunc.xll
add-in. You'll have to search the Web to find a download site; and there are
some limitations as well as advantages.

If you mean something else, let us know.

--ron
 
Hi,

Regular expressions is over the top for doing what you wany, try this

=LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW($1:$10000))))

Mike
--
Mike

When competing hypotheses are equal, adopt the hypothesis that introduces
the fewest assumptions while still sufficiently answering the question.
Occam''''s razor (Abbrev)
 
Back
Top