The following approach simply texts characters for VALUE()
and keeps only those that pass the test (and hence are numerals). It then strings them together and uses VALUE()
again, this time to change them from string to number:
=VALUE(TEXTJOIN("",TRUE,IFERROR(VALUE(MID(A1,SEQUENCE(1,LEN(A1)),1)),"")))
This is 2021 so I used TEXTJOIN()
and SEQUENCE()
but one could use CONCAT()
and the old "ROW(1:xxx)" trick instead for a more 2016-y feel.
You'd also have to use {CSE}, I suppose, but that's no trick.
This way the return is simply the digits present, as the question asks for. It does, and learns, nothing else.
Since no other characters CAN be kept, it doesn't matter whether they are bizarre, non-printing blank characters or not, and so it doesn't matter either, just what they are. Nothing else will be kept and that's ok as nothing else was desired.
A last peer at the data makes me wonder if row 296 in the pic has a decimal point. If so, one could keep it using the following (with CONCAT()
in place of TEXTJOIN()
just to show it works nicely):
=VALUE(CONCAT(IF(MID(A1,SEQUENCE(1,LEN(A1)),1)=".",MID(A1,SEQUENCE(1,LEN(A1)),1),IFERROR(VALUE(MID(A1,SEQUENCE(1,LEN(A1)),1)),""))))
One is testing characters for the match to a decimal point THEN performing the check for numeralcy. Order is important, and other tests would go first as well.