I have a cell in excel which holds a 6 digit number which I need to extract. That cell may contain stings or other numbers but fortunately no other number of 6 digits. (spacing, number of words, location number may vary) Is there a way to extract that 6 digit number only?
I cannot use VBA for this, if must be a formula
| Cell A1 | Cell B1 |
|---|---|
| ab 12 abcd 1234 abcdef 123456 abcdefgh 12345678 | 123456 |
It's safer to test each individual character within the parsed substrings for numericalness. Otherwise, substrings such as 2-june or 1.2E04 can be returned undesirably.
=LET(
ζ,TEXTSPLIT(A1," "),
FILTER(ζ,(LEN(ζ)=6)*MMULT(SEQUENCE(,6,,0),1-ISERR(0+MID(ζ,SEQUENCE(6),1)))=6)
)
That said, the following should also be sufficiently rigorous:
=LET(ζ,TEXTSPLIT(A1," "),FILTER(ζ,(LEN(ζ)=6)*(1-ISERR(0+(ζ&"**0")))))
though the latter will return decimals such as 1.2345 whereas the former won't.