Extract 6-digit numbers from cells with alphanumeric text

[Mike_1234]

Hi


Thank you for this amazing site and volunteers!

Your answers to other queries have been much appreciated!

I get invoice numbers (6 digits) included in mixed data (Column A below)

I need to only extract the first one or two such invoice numbers which are defined as:

6 consecutive digits
always with a space or comma before - or they are the start of the cell
always with a space or comma after - or they are the end of the cell

If no such invoice numbers, should return blank
If there are 3 or more such numbers, we treat it differently, so should return blank

The numbers are anywhere in the cell as shown in table below (I've also attached this in Excel)

Cells	Result	Result description
'RENTAL CERTIFICATE, 196595 , FP TRN	196595	6-digit number extracted
''RENTAL CERTIFICATE 234, 196762 , FP TRDHYTR	196762	6-digit number extracted, ignored prior shorter number (234)
'RENTAL CERTIFICATE, 1964470 196657 , FP 24/11/22 2011 1964470 , TRN12	196657	6-digit number extracted, ignored all longer and shorter numbers
'RENTAL CERTIFICATE, 194966 and 194967 FP 24/11/22	194966 194967	2 x 6-digit-numbers extracted - despite letters/spaces between
'PUPSPACE 1234 LIMITED , 194932 194948 195000, FP 25/11/22 0248 , 04023		3 x 6-digit numbers detected - so returns blank
ARL PRO HOLD , FP 25/11/22 1415 , 2211255568545300		no 6-digit numbers detected - so returns blank
196765 , VIA MOBILE - LVP	196765	6-digit number extracted (start)
'PRESTIGE RANGES, 196282	196282	6-digit number extracted (end)

All help given much appreciated.

Our computer uses: "Microsoft® Excel® 2019 MSO" - I am looking for an Excel solution not VBA etc

I already read this page
https://chandoo.org/wp/extract-numbers-from-text-excel/
The suggestions on were helpful, but extracted the first "number" of any length

for example this one from Justin: (thanks!)
=IFERROR(LOOKUP(9.99999999999999E+307,--MID(A1,MIN(FIND(ROW($1:$10)-{1},A1&56^7)),ROW(INDIRECT("1:"&LEN(A1))))),"")

But my query is different

Thanks so much to all of you

All the best


Mike


PS: Last point:
i didn't want to put this in case it makes it impossible / too difficult, but would it be possible for the formula to only "recognize" 6 digit numbers between x and y?
(eg 170,000 to 200,000)
and for this range to be changeable in the future, either by editing the formula itself or by referring to 2 cells in the worksheet which will (currently) show 170,000 and 200,000)?
So that any 6-digit number outside that range is treated as any non-6-digit number - it will not be displayed, or count toward the 3 instances
If this point makes the whole thing too much, please don't bother with it! Thanks!

 

[bosco_yip]

.............
PS: Last point:
i didn't want to put this in case it makes it impossible / too difficult, but would it be possible for the formula to only "recognize" 6 digit numbers between x and y?
(eg 170,000 to 200,000)
and for this range to be changeable in the future, either by editing the formula itself or by referring to 2 cells in the worksheet which will (currently) show 170,000 and 200,000)?
So that any 6-digit number outside that range is treated as any non-6-digit number - it will not be displayed, or count toward the 3 instances
If this point makes the whole thing too much, please don't bother with it! Thanks!

Mike,

Please give/add examples of the below "Last point" as mentioned and resubmit your revised file to us.

And please don't change your requirements in anyway.

Please also inform us, what type of Excel version you are using?

Regards

 

[Mike_1234]

Mike,

Please give/add examples of the below "Last point" as mentioned and resubmit your revised file to us.

And please don't change your requirements in anyway.

Please also inform us, what type of Excel version you are using?

Regards

Hi Bosco

Thank you for your reply!

For the "Last point" harder challenge about the number range, I have attached it as a revised file. The
start and end of the number range are in cells on the top row

All the best

Mike

 

[GraH - Guido]

With Power Query this seems rather simple.
Split the text on the delimters space and comma.
This gives a list of argumentes.
Transform each member of the list to a number.
Select from this list anything with a lenght of 6.
What if a number would start with 0? So maybe the last two steps must be inversed. Then we must add a step where errors are ignored. Finaly we test if there are only 2 or less members in that list. If so concatenate the list back to a string. Else return blank.

No access to my pc for the moment, so I can't upload a sample. Later, maybe...

 

[bosco_yip]

Try this formula solution, worked for Excel 2019 and above version

1] Select C2 >> Define Name >>

• Name: SplitNum
• Refers to: =FILTERXML("<a><b>"&SUBSTITUTE(SUBSTITUTE($A2,","," ")," ","</b><b>")&"</b></a>","a/b[.>="&G$1&"][.<="&J$1&"]")
• OK

Then

2] In C2, enter formula and copied down:

=TEXTJOIN(" ",,IF(COUNT(SplitNum)>2,"",IFERROR(SplitNum,"")))



Or,

If you have Excel 2021 with LET function, you can use this single function

In C2, enter formula and copied down:

=TEXTJOIN(" ",,LET(substrings,FILTERXML("<a><b>"&SUBSTITUTE(SUBSTITUTE($A2,","," ")," ","</b><b>")&"</b></a>","a/b[.>="&G$1&"][.<="&J$1&"]"),criterion,IF(COUNT(substrings)>2,"",IFERROR(substrings,"")),criterion))

Regards

 

[Peter Bartholomew]

I don't think you mentioned an Excel version. As a 365 user, I would define a Lambda function to operate on a single cell

= LAMBDA(cellRef,
    LET(
      substrings, TEXTSPLIT(cellRef, {" ",","}),
      criterion,  ISNUMBER(--substrings)*(LEN(substrings)=6),
      invoices,   IF(SUM(criterion)<=2, FILTER(substrings, criterion, ""), ""),
      TEXTJOIN(CHAR(10),, invoices)
    )
  )

and then call it using MAP

= MAP(InvoiceData, FindInvoiceλ)

 

[GraH - Guido]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Cells", type text}}),
    #"6 digit numbers" = Table.AddColumn(#"Changed Type", "6 digit numbers", each
            let
                _list = Text.SplitAny([Cells], ", "),
                _selection = List.Select(_list, each Text.Length(_) = 6),
                _numbers = List.Transform(_selection, each try Text.From(Number.FromText(_)) otherwise null),
                _no_nulls = List.RemoveNulls(_numbers),
                _count = List.Count(_no_nulls)
            in
                if _count >2 then null else Text.Combine(_no_nulls, ", "))
in
    #"6 digit numbers"