+ Reply to Thread
Results 1 to 5 of 5

Return a Suppler name from a text string

  1. #1
    Registered User
    Join Date
    11-12-2019
    Location
    Darlington, England
    MS-Off Ver
    Excel 2010
    Posts
    7

    Return a Suppler name from a text string

    Hi,
    thanks for looking?
    When I download some nominal data from the finance system into excel the supplier number (which is a mixture of letters and numbers) appears either at the start, middle or the end of a text string including other information. Therefore if I am wanting to do a vlookup against this information to get the supplier name against the supplier master table (supplier number in one row and supplier name in the other).
    I have a master list of the supplier numbers in one column, supplier name in another column, that the formula can lookup to and hopefully it can return the supplier name as the result.
    I am not sure whether the formula I am looking for is like a contain formula, if it contains the supplier number return the supplier name from the list.
    Hope this makes sense, any ideas?

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Return a Suppler name from a text string

    I'd recommend following instruction in the banner at top.
    Fast answers need clear examples. Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. Just before posting, scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.
    If unable to. Typical construct would be something like...
    =LOOKUP(2,1/SEARCH("SomeString",List),Column Holding Value to return)
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein

  3. #3
    Registered User
    Join Date
    11-12-2019
    Location
    Darlington, England
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Return a Suppler name from a text string

    Thank you...

    From the below example, if I have a mass of data - here is the example of one cell where I want to get the supplier number into a separate cell and from this number I can easily add in the supplier name

    Text String
    4filesMITR003plus less five



    Lookup table Range
    Supplier Number Supplier Name
    JASO001 Jasons Shoes Ltd
    MITR003 Mire Ball Co.
    JONN004 Jonathan Sports Corp

  4. #4
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Return a Suppler name from a text string

    Assuming string is in A1. Lookup table in D1:E4 including header.

    =LOOKUP(2,1/SEARCH($D$2:$D$4,A1),$E$2:$E$4)

    See attached.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    11-12-2019
    Location
    Darlington, England
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Return a Suppler name from a text string

    Cheers thank you very much that works spot on - I owe you a beer

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 5
    Last Post: 07-10-2017, 10:36 AM
  2. Replies: 2
    Last Post: 05-14-2015, 01:30 PM
  3. Replies: 4
    Last Post: 03-30-2015, 03:20 PM
  4. [SOLVED] Search for a text string and return given text string to adjacent cell
    By hecgroups in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-14-2015, 04:50 AM
  5. [SOLVED] IF range of cells contains string of text, return contents of cell where string is found
    By nobodyukno in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 07-03-2013, 09:56 AM
  6. [SOLVED] Find partial text string within another text string return original text into cell.
    By mikey42979 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 06-17-2013, 02:58 PM
  7. Replies: 2
    Last Post: 02-26-2008, 03:22 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1