+ Reply to Thread
Results 1 to 9 of 9

Code/Solution To Search All Text in a Cell and Pull UniqueID Corresponding to the Text

  1. #1
    Registered User
    Join Date
    08-05-2013
    Location
    Boston, MA
    MS-Off Ver
    Excel 2010 for Mac
    Posts
    60

    Code/Solution To Search All Text in a Cell and Pull UniqueID Corresponding to the Text

    I am trying to find a solution that gives me the unique ID corresponding to a list of names

    Computer Programming
    Programmer
    Technician
    etc
    etc

    referring to table:

    Computer Programming/Programmer, General 11.0201
    Data Processing and Data Processing Technology/Technician 11.0301
    Data Entry/Microcomputer Applications 11.0601
    Web Page, Digital.Multimedia and Information Resources Design 11.0801
    Computer Systems Networking and Telecommunications 11.0901
    Network and System Administration/Administrator 11.1001

    I want to find the corresponding value of the "Name" using VLOOKUP (or another way if you guys have any suggestions). The problem is that the cells containing the names such as "Computer Programming" or "Programmer" or "General" won't find these specific names, e.g., Programmer or General when I use wildcat in my VLOOKUP function, e.g., "*"&B5&"*" but it will find the code 11.0201 for something that comes first in the cell it searches for, such as Computer Programming since it comes first in the cell.

    Is there a way for me to use a function or piece of code that goes through each row in a column and searches the box for text containing what I am looking for, and gives me the unique ID corresponding to the box?

  2. #2
    Valued Forum Contributor
    Join Date
    10-26-2008
    Location
    Birmingham, UK
    MS-Off Ver
    All versions up to 2010
    Posts
    1,025

    Re: Code/Solution To Search All Text in a Cell and Pull UniqueID Corresponding to the Text

    Hi
    Try putting an asterisk at end end of the search phrase and still use the cell reference in your VLOOKUP. It works for me.
    For example your cell reference would contain *Programmer* when searching for Programmer.
    Hope this helps.
    Good luck.
    Tony

  3. #3
    Forum Expert JasperD's Avatar
    Join Date
    05-07-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    1,393

    Re: Code/Solution To Search All Text in a Cell and Pull UniqueID Corresponding to the Text

    VBA is the way to go.
    Can you upload a sample workbook, please?
    Please click the * below if this helps

  4. #4
    Registered User
    Join Date
    08-05-2013
    Location
    Boston, MA
    MS-Off Ver
    Excel 2010 for Mac
    Posts
    60

    Re: Code/Solution To Search All Text in a Cell and Pull UniqueID Corresponding to the Text

    Here it is. I get some results but N/As for a lot, so it is not searching the whole cell, but only the beginning (so it will find results if the name is the first part of the cell, but not if it comes after a /).
    Attached Files Attached Files

  5. #5
    Forum Expert JasperD's Avatar
    Join Date
    05-07-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    1,393

    Re: Code/Solution To Search All Text in a Cell and Pull UniqueID Corresponding to the Text

    Try this :
    Please Login or Register  to view this content.
    Please click the * below if this helps

  6. #6
    Valued Forum Contributor
    Join Date
    10-26-2008
    Location
    Birmingham, UK
    MS-Off Ver
    All versions up to 2010
    Posts
    1,025

    Re: Code/Solution To Search All Text in a Cell and Pull UniqueID Corresponding to the Text

    Hi
    You are getting the #N/A entries because the data you are looking for does not exist in the LOOKUP table.
    Tony

  7. #7
    Forum Contributor
    Join Date
    08-11-2012
    Location
    bengalur
    MS-Off Ver
    Excel 2003, 2007
    Posts
    152

    Re: Code/Solution To Search All Text in a Cell and Pull UniqueID Corresponding to the Text

    Here is the vlookup which gives the results of the Profession Code for different Profession.

    Below is the formula used =VLOOKUP("*"&B2&"*",$G$1:$H$6,2,0)

  8. #8
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,445

    Re: Code/Solution To Search All Text in a Cell and Pull UniqueID Corresponding to the Text

    Please Login or Register  to view this content.
    works perfect
    Last edited by AB33; 08-06-2013 at 10:56 AM.

  9. #9
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,445

    Re: Code/Solution To Search All Text in a Cell and Pull UniqueID Corresponding to the Text

    This seem to work too

    Please Login or Register  to view this content.

+ 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. [SOLVED] search for text, then search for previous text, replace 1st text including number from 2nd
    By kldailey in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-06-2012, 05:23 PM
  2. [SOLVED] Require vba solution for text box "text" transfer to cell
    By Kartman in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-22-2012, 03:57 PM
  3. [SOLVED] VBA to search number/text and pull information from access into excel
    By stephen11 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-24-2012, 08:44 AM
  4. Search within text and IF solution needed
    By sc1775 in forum Excel General
    Replies: 3
    Last Post: 03-30-2011, 03:16 PM
  5. Replies: 1
    Last Post: 04-12-2006, 05:00 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