PrecisionCalc
inspector
text
Do Anything with Text in Formulas
itEXTRACTNUM
Retrieves numeric and other desired characters from text, detecting the local decimal symbol, negative numbers, and exponential notation.
Decimal:
The first decimal symbol found (starting from the left) is extracted (unless the decimal symbol is entered as a numlist argument).
Any other decimal symbols found are not extracted.
The decimal symbol is localized. For example, an itEXTRACTNUM formula that extracts "1.23" in the USA will extract "1,23" in Germany and France.
Negative Numbers:
itEXTRACTNUM recognizes the resulting number as negative if:
It finds a hyphen to the left of any numeric digits or decimal, or
It finds a left parenthesis to the left of any numeric digit or decimal and a right parenthesis to the right of any numeric digit or decimal.
Exponential Notation:
itEXTRACTNUM recognizes the resulting number as being in exponential notation if ALL of the following are true:
The exponential notation is in the form "E+" or "E-".
The "E+" or "E-" is to the right of the base number and to the left of the exponent.
For example, 1.2E+3 = 1200 and 1.2E-3 = 0.0012
No more than one numeric digit appears to the left of the decimal symbol (but it is not necessary to have a digit to the left of the decimal).
For example, .2E+3 = 0.2E+3
If no decimal is found, there is one and only one numeric digit to the left of the "E".
For example, 2E+3 = 2000 and 2E-3 = 0.002
One or more numeric digits appear after the "E+" or "E-".
If a decimal symbol is found, it must be to the left of "E".
A "+" or "-" is found after "E", and no numeric digits or decimal symbol are found between "E" and the "+" or "-".
If "E" alone (without a "+" after it) should indicate a positive exponent, nest Microsoft Excel's SUBSTITUTE function inside itEXTRACTNUM to change all occurrences of "E" to "E+". See example below.
The decimal symbol is localized. For example, an itEXTRACTNUM formula that extracts "1.2E+3" in the USA will extract "1,2E+3" in Germany and France.
Syntax
itEXTRACTNUM(text,extract_list...)
| text | Required. The
text from which to extract numeric and other desired characters. text does not appear in Microsoft Excel's 'Insert Function' dialog. Instead, the first extract_list argument is considered the text argument. If the first extract_list argument is a range of cells, then the upper-left cell is considered the text argument. |
| extractlist... | Optional. The list of characters and/or text strings to extract from text (in addition to numeric characters, which are extracted by default). Accepts up to 29 numlist arguments. |
Examples
| Formula | Description | Result | |||||||||
| =itEXTRACTNUM("12abc34") | Extract numeric characters from "12abc34" | 1234 | |||||||||
| =itEXTRACTNUM("1-a2~b.c%3d)&(e@f4") | Extract numeric characters from "1-a2~b.c%3d)&(e@f4" | 12.34 | |||||||||
| =itEXTRACTNUM("-1(2)abc.34") | Extract numeric characters from "-1(2)abc.34" | -12.34 | |||||||||
| =itEXTRACTNUM("E+~a-!1b@E.c#2d$Ef%-g^5h&E+") | Extract numeric characters from "E+~a-!1b@E.c#2d$Ef%-g^5h&E+" | -1.2E-5 | |||||||||
| =itEXTRACTNUM("x1.2E5") | Extract numeric characters from "x1.2E5". Note, the "E" is not followed by a "+" or "-". | 1.25 | |||||||||
| =itEXTRACTNUM(SUBSTITUTE("x1.2E5","E","E+")) | Change all occurrences of "E" in "x1.2E5" to "E+", then extract numeric characters. | 1.2E+5 | |||||||||
| =itEXTRACTNUM("1.2abc34","a") | Extract numeric characters, and also extract "a", from "1.2abc34" | 1.2a34 | |||||||||
=itEXTRACTNUM(A1:B2)
|
Extract numeric characters, and also extract values in cells A1:B2 (except A1, because the first argument is a range and A1 is the upper-left corner of the range) from the value in cell A1 | 12abc34 |
See Also
itEXCLUDE
itEXCLUDENOTNUM
itEXTRACT