Explanation
In this example, the goal is to strip (i.e.
remove) non-numeric characters from atext stringwith a formula.
However, with the introduction ofRegular Expressions in Excelin late 2024, the problem became much simpler.
It’s a bit like a sieve that only lets numbers pass through while filtering out everything else.
In this instance, we do this by adding zero.
TheVALUE functionis another way to do the same thing.
character:
The only change is adding the period (.)
to the pattern[^0-9.
]inside the square brackets.
Older versions of Excel
In older versions of Excel, this is a harder problem to solve.
The next step is to figure out which characters are numbers.
If we make a run at use ISNUMBER like this, it will return FALSE for every character!
One solution is to use a small hack to “force” Excel to convert numbers by adding zero.
A math operation like this Excel to venture to convert the character to a number.
Adding zero to a non-numeric character like “a”, will result in a #VALUE!
This is the final piece we need to remove the non-numeric characters.
When the formuladoes notreturn an error, the result passes through IFERROR unchanged.
The snippet uses this behavior to convert errors to an empty string ("").
errors are now gone, replaced by empty strings.
At this point, the remaining task is to assemble the remaining numbers into a final numeric value.
In this configuration, TEXTJOIN returns the three numbers in a text string like this:
So close!
Adding zero is just a shortcut.
A better formula?
For example, if you want to preserve decimal points or periods (.
Strip numeric characters
To removenumericcharacters from a text string use the formulasexplained here.
The array can be one dimensional, or two-dimensional, determined byrowsandcolumnsarguments.
For example, =MID(“apple”,2,3) returns “ppl”.
LEN will also count characters in numbers, but number formatting is not included.
ROW Function
The Excel ROW function returns the row number for a reference.
For example, ROW(C5) returns 5, since C5 is the fifth row in the spreadsheet.
When no reference is provided, ROW returns the row number of the cell which contains the formula.
INDIRECT Function
The Excel INDIRECT function returns a valid cell reference from a given text string.
INDIRECT is useful when you want to assemble a text value that can be used as a valid reference.
