Typically the length of the text is 100-300 letter & number characters in the cell. I don't want dates or shorter number than 8 digits. As an example the cells will have combinations of text like this:
DC ID 282261, PO 12345400, and also 22215212, PETBLANKET, 11.14.23||REALLOCATION PROJECT ORIGINAL PO 22585122 AND NEW PO 12877441, 07.26.23, GVAC || 5.3.23/Re-added to the CPTT/SW updated and Re-approved/Admiral||4.25 video-game series, players per APM WBEX || 4.24 updated units per PP WBEX CONFIRMED PO HAS BEEN 100% RCVD BY DCS. CONFIRMED BY TRANSPORTATION 0% SHORTAGE. No cards in their deck. 11/8/23 PO 12994129 closed due to PP migration
The clean result I hope for is something close to this:
12345400, 22215212, 22585122, 12877441, 12994129
or putting them in separate columns to the right
I have tried this formula and only get the 1st occurrence when there are 5 total in the cell:
=LET(ζ,TEXTSPLIT(B6," "),FILTER(ζ,(LEN(ζ)=8)*MMULT(SEQUENCE(,8,,0),1-ISERR(0+MID(ζ,SEQUENCE(8),1)))=8))
result: 12345400
And there is this formula that pulls every single number, regardless of the length, with no spaces or delimiters between the numbers, returning a long string of digits (containing dates etc that I hope to filter out):
=TEXTJOIN(, 1, TEXT(MID(B6, ROW($AB$1:INDEX($B$1:$B$1000, LEN(B6))), 1), "#;-#;0;"))
result: 28226112345400222152121114232258512212877441072623532342542410001182312994129
Please can anyone help me merge the above formulas or suggest a different one entirely?
If you have a tool that supports searching with regular expression, you just need to search by
\d{8}to match on strictly the 8 digit decimal numbers you’re looking for. See: http://www.regexr.com/7roem and check the “List” at the bottom for a formatted output.