Having trouble extracting multiple numbers from a data filled tab!!

Hi Excel Experts

I am having a bit of a problem with a project. I have a list of unsorted random data, and I need to extract a certain type of invoice number.

Example
A B C
1 [ InvoiceNr12006210..Nr.12006543 CustomerNr.056321 paid ]
2 [ Invoice 12001120 CustomerNr04624 Invoice Nr12008009 ]
3 ............
4 ......
5 .... and so on.

I only need to take out invoice numbers so anything with 1200. So far I have used =MID(A1,Find("1200",A1)+0,8)
But it only gives me the first invoice number it finds. I have a feeling I should be using the "IF" fuction some way but I can't figure it out.


If anyone could help that would be amazing.

Thanks a million

View attachment Solution.xlsx

moses360 said:

Hi Excel Experts

I am having a bit of a problem with a project. I have a list of unsorted random data, and I need to extract a certain type of invoice number.

Example
A B C
1 [ InvoiceNr12006210..Nr.12006543 CustomerNr.056321 paid ]
2 [ Invoice 12001120 CustomerNr04624 Invoice Nr12008009 ]
3 ............
4 ......
5 .... and so on.

I only need to take out invoice numbers so anything with 1200. So far I have used =MID(A1,Find("1200",A1)+0,8)
But it only gives me the first invoice number it finds. I have a feeling I should be using the "IF" fuction some way but I can't figure it out.


If anyone could help that would be amazing.

Thanks a million

Click to expand...

Please see attached file. I assume you only have 2 invoices in each line.

With the use of a helper column ( which you can hide or put anywhere on the sheet), I came up with the attached which will work for any number of invoice numbers. Just drag the formula starting in col C as far as needed
I assume that all invoice numbers starting with 1200 are 8 digits long?
Also assumed that you are using a post 2003 version
If not you will have to use a " IF(ISERROR(..." condition instead if IFERROR

View attachment extract invoice.xlsx

Sorry for the late reply. That formula worked brilliantly for me.
Thank you so much