Results 1 to 4 of 4

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

  1. #1

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Solution.xlsx
    Quote Originally Posted by moses360 View Post
    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
    Please see attached file. I assume you only have 2 invoices in each line.

  3. #3
    Super Moderator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,467
    Articles
    0
    Excel Version
    2010 on Xubuntu
    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

    extract invoice.xlsx

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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •