Best formula/method to extract a standard set of numbers from a string?-CodePudding

I have the following strings from which I need to extract 6 digit numbers. Since these strings are generated by another software, they occur interchangeably and I cannot control it. Is there any one method that would extract both 6-digit numbers from each of these strings?

Branch '100235 to 100236 Ckt 1' specified in table 'East Contingency' for record with primary key = 21733 was not found in branch or transformer data.

Loadflow branch ID '256574_701027_1' defined in supplemental branch table was not found in branch or transformer input.

Transmission element from bus number 135415 to bus number 157062 circuit ID = 1 defined for corridor 'IESO-NYISO' was not found in input data

I don't know VBA, but I can learn it if it means I can get the 6 digit numbers using a single method. thanks

I have been using LEFT(), RIGHT() & MID() previously, but it means manually applying the appropriate formula for individual string.

CodePudding user response：

If you have Microsoft 365, you can use this formula:

=LET(arr,TEXTSPLIT(SUBSTITUTE(SUBSTITUTE(A1,"'"," "),"_"," ")," "),
FILTER(arr,ISNUMBER(-arr)*(LEN(arr)=6)))

Data

Output

CodePudding user response：

An alternative is:

=LET(ζ,MID(A1,SEQUENCE(,LEN(A1)-5),6),ξ,MID(ζ,SEQUENCE(6),1),FILTER(ζ,MMULT(SEQUENCE(,6,,0),1-ISERR(0 ξ))=6))