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)

 A B 1 12abcde34 b 2 a c
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