PrecisionCalc
inspector
text
Do Anything with Text in Formulas

itEXCLUDENOTNUM

Excludes non-numeric and other undesired characters and/or digits from text, detecting the local decimal symbol, negative numbers, and exponential notation.

Decimal:

• The first decimal symbol found (starting from the left) is not excluded (unless the decimal symbol is entered as a numlist argument).

• Any other decimal symbols found are excluded.

• The decimal symbol is localized. For example, an itEXCLUDENOTNUM formula that returns "1.23" in the USA will return "1,23" in Germany and France.

Negative Numbers:

• itEXCLUDENOTNUM 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:

• itEXCLUDENOTNUM 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 itEXCLUDENOTNUM to change all occurrences of "E" to "E+". See example below.

• The decimal symbol is localized. For example, an itEXCLUDENOTNUM formula that returns "1.2E+3" in the USA will return "1,2E+3" in Germany and France.

Syntax

itEXCLUDENOTNUM(text,exclude_list...)

 text Required. The number or text from which to exclude non-numeric and other characters and/or digits. num does not appear in Microsoft Excel's 'Insert Function' dialog. Instead, the first numlist argument is considered the num argument. If the first numlist argument is a range of cells, then the upper-left cell is considered the num argument. exclude_list... Optional. The list of characters, text strings, and/or digits to exclude from num (in addition to non-numeric characters, which are excluded by default). Accepts up to 29 numlist arguments, or up to 28 if the optional format argument is used.

Examples

Formula Description Result
=itEXCLUDENOTNUM("12abc34") Exclude non-numeric characters from "12abc34" 1234
=itEXCLUDENOTNUM("1-a2~b.c%3d)&(e@f4") Exclude non-numeric characters from "1-a2~b.c%3d)&(e@f4" 12.34
=itEXCLUDENOTNUM("-1(2)abc.34") Exclude non-numeric characters from "-1(2)abc.34" -12.34
=itEXCLUDENOTNUM("E+~a-!1b@E.c#2d\$Ef%-g^5h&E+") Exclude non-numeric characters from "E+~a-!1b@E.c#2d\$Ef%-g^5h&E+" -1.2E-5
=itEXCLUDENOTNUM("x1.2E5") Exclude non-numeric characters from "x1.2E5". Note, the "E" is not followed by a "+" or "-". 1.25
=itEXCLUDENOTNUM(SUBSTITUTE("x1.2E5","E","E+")) Change all occurrences of "E" in "x1.2E5" to "E+", then exclude non-numeric characters. 1.2E+5
=itEXCLUDENOTNUM("1.2abc34",".") Exclude non-numeric characters, and also exclude ".", from "1.2abc34" 1234
=itEXCLUDENOTNUM(A1:B2)

 A B 1 12abc34 2 2 1 3
Exclude non-numeric characters, and also exclude 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 4