Home |
Search |
Today's Posts |
#20
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need formula to extract a numeric value from a free-format tex
In case you want to consider it, here is a non-RegEx array-entered** formula
that will do what you want... =MID(F5,MIN(IF(ISNUMBER(--MID(SUBSTITUTE(F5," ","x"),ROW(1:30),7))* ISERR(SEARCH("e",MID(F5,ROW(1:30),7)))*ISERR(FIND( "/",MID(F5,ROW (1:30),7))),ROW(1:30))),7) Note though, that this formula is dependent on what your default date separator is. Mine is the slash character (/) and that is what I used in the FIND function call... if your default date separator is a different symbol, then just replace my slash with that character. -- Rick (MVP - Excel) "Eric_NY" wrote in message ... I just read your message from last Friday. The text is free format. Users can enter it in whatever format they want. The 7-digit number is somewhere within the text. I've glanced through it and in the samples I've seen, there's no consistency in what appears before or after the 7-digit number. I used the regex solution that Ron Rosenfeld suggested, and adjusted the regular expression by removing the "\b" before and after the "\d{7}". "Rick Rothstein" wrote: I would still be interested in the answer to my questions... "Is the number always precede by a space when the number is interior to the text? What about that "dot" after it... is there always a dot following it? If the dot might not always be there, is there always a space after the number when it is interior to the text?" -- Rick (MVP - Excel) "Eric_NY" wrote in message ... "However, with the morefunc add-in there is an option, I believe it is a menu option, to install the add-in as part of the workbook. " I can't find that option. Where should I look for it? Which menu? "Ron Rosenfeld" wrote: On Thu, 16 Jul 2009 12:04:04 -0700, Eric_NY wrote: Ron - Thanks. I'm going to try the Longre morefunc add-in. If I use the add-in functions and then send the sheet to someone by email, will the formulas still work? Or does the recipient also have to install the add-in separately on his own machine? Thanks for your help. Ordinarily no. They would have to install it themselves. However, with the morefunc add-in there is an option, I believe it is a menu option, to install the add-in as part of the workbook. If you do that, it will then be usable by the recipient with no particular effort on his part. One caution concerning the add-in -- it will not work on strings that are longer than 255 characters. This, apparently is an .xll limitation, and there is no good way around it within the add-in. By the way, if you should use the UDF approach, the UDF should be embedded within the workbook, so its use should be transparent to your user. But there are a lot of other useful functions in morefunc. --ron |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find text in free-format text field | Excel Discussion (Misc queries) | |||
Extract just numeric part of mixed text/number entry? | Excel Worksheet Functions | |||
only extract numeric value from alpha numeric cell | Excel Discussion (Misc queries) | |||
Change number (in text format) to numeric format | Excel Discussion (Misc queries) | |||
Everytime I need to format cells or text, Excel 2003 takes a lot of time or free | Excel Discussion (Misc queries) |