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)
|
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 |
See Also
itEXCLUDE
itEXTRACT
itEXTRACTNUM