extract customer name and address from huge data dump in every cell

    Happy Friday Oz!



    I have a dataset where the data in nearly every cell in column A reaches excel's cap of 32,762 characters. Each cell contains a single customer's info, along with other useless data, repeated over and over in a pattern I can't quite decipher.



    What I have noticed is, if I select the last occurrence of "Customer Info" as my mid point and then back up to the next left most occurrence of "MemberDirectory" as my left boundary, and then go forward to the first occurrence of "OHBA" as the right boundary (leaving "Customer Info" in the middle as my MID point), the customer information I need appears between these two boundaries. If anyone could help me write a formula to do this properly I would much appreciate it! I have attached a worksheet with some sample data. Thanks Oz!

    Re: extract customer name and address from huge data dump in every cell


    My starting point woudl be to get rid of as much of the extraneous text as you can like this:


    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"OHBA",""),"Member",""),"Directory","")

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

    Re: extract customer name and address from huge data dump in every cell


    Thank you for replying AliGW!



    I have tried that and, unfortunately, it left me with dozens of copies of the company name as well as 4 instances of "Contact Info", 2 of which actually have the customer's name trailing. And without having a preceding "MemberDirectory" followed by an "OHBA" as goal posts on either side of the last "Contact Info", I don't know how else to target what I'm after. Thanks anyway!

    Re: extract customer name and address from huge data dump in every cell


    A little more info: In column A, each cell has thousands of rows filled with OHBA and Member Directory repeated over and over, a small portion of which is shown here. The company name also appears many times. In a few places, you will see everything come together neatly where the company name and address, followed by Contact Info and the contact's name, phone, email and/or website appears. Ideally, each element would be extracted to a separate column but for now I would be content just to get this block of data out.



    EXAMPLE 1



    OHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberDirectory
    Alterra Group of CompaniesOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberDirectory
    OHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberDirectory
    Alterra Group of CompaniesAddress



    920 Yonge St. Suite 1000
    Toronto
    , ON
    M4W 3C7



    Contact Info



    Robert Cooper
    (416) 964-1800
    [email protected]
    alterra.com



    OHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberDirectory
    OHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberDirectory
    Alterra Group of CompaniesOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberDirectory



    EXAMPLE 2



    Altra Homes Inc.OHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberDirectory
    OHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberDirectory
    Altra Homes Inc.Address



    345 Lakeshore Rd. E. Unit 402
    Oakville
    , ON
    L6J 1J5



    Contact Info



    Tim Le Bas de Plumetot
    (647) 533-8016



    altrahomes.com



    OHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberDirectory
    OHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberDirectory
    Altra Homes Inc.OHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberDirectory



    EXAMPLE 3



    Amacon Construction LimitedOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberDirectory
    OHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberDirectory
    Amacon Construction LimitedAddress



    37 Bay St. Suite 400
    Toronto
    , ON
    M5J 3B2



    Contact Info



    Frank Da Silva
    (416) 369-9069
    [email protected]
    amacon.com



    OHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberDirectory
    OHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberDirectory

    Re: extract customer name and address from huge data dump in every cell


    Wow ok that's a crappy data feed.


    Instead of trying to find a solution you all ready have it.


    Instead of trying to make a macro to do what you've done with the formulas. Use a macro to just put new data into the A1 cell and then copy out the F1 cell?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login