PrecisionCalc
inspector
text
Do Anything with Text in Formulas
itEXCLUDENOTNUM
Excludes nonnumeric 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.2E3 = 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 2E3 = 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 nonnumeric 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 upperleft 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 nonnumeric 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 nonnumeric characters from "12abc34"  1234  
=itEXCLUDENOTNUM("1a2~b.c%3d)&(e@f4")  Exclude nonnumeric characters from "1a2~b.c%3d)&(e@f4"  12.34  
=itEXCLUDENOTNUM("1(2)abc.34")  Exclude nonnumeric characters from "1(2)abc.34"  12.34  
=itEXCLUDENOTNUM("E+~a!1b@E.c#2d$Ef%g^5h&E+")  Exclude nonnumeric characters from "E+~a!1b@E.c#2d$Ef%g^5h&E+"  1.2E5  
=itEXCLUDENOTNUM("x1.2E5")  Exclude nonnumeric 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 nonnumeric characters.  1.2E+5  
=itEXCLUDENOTNUM("1.2abc34",".")  Exclude nonnumeric characters, and also exclude ".", from "1.2abc34"  1234  
=itEXCLUDENOTNUM(A1:B2)

Exclude nonnumeric characters, and also exclude values in cells A1:B2 (except A1, because the first argument is a range and A1 is the upperleft corner of the range) from the value in cell A1  4 
See Also
itEXCLUDE
itEXTRACT
itEXTRACTNUM