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)
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!
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!
Attachments
Last edited: