Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
what are the regular expression special characters while searching
Search looking for numbers embedded in text, can you use [0-9]+
My address is 1235 Main Street Your address is 34 Elm Street |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
what are the regular expression special characters while searching
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 -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "Howdy" wrote in message ... Search looking for numbers embedded in text, can you use [0-9]+ My address is 1235 Main Street Your address is 34 Elm Street |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
what are the regular expression special characters while searching
On Mon, 18 Jan 2010 05:19:02 -0800, Howdy
wrote: Search looking for numbers embedded in text, can you use [0-9]+ My address is 1235 Main Street Your address is 34 Elm Street 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
what are the regular expression special characters while searching
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&"0 123456789")),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) "Howdy" wrote: Search looking for numbers embedded in text, can you use [0-9]+ My address is 1235 Main Street Your address is 34 Elm Street |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Searching Uppercase characters | Excel Discussion (Misc queries) | |||
Can someone help me with this regular expression? | Excel Discussion (Misc queries) | |||
Regular expression search/replace in Excel | Excel Discussion (Misc queries) | |||
Excel formula needed... regular expression? | Excel Discussion (Misc queries) | |||
Regular Expression | Excel Discussion (Misc queries) |