+ Reply to Thread
Results 1 to 12 of 12

macro to search text & return values

  1. #1
    Registered User
    Join Date
    01-09-2009
    Location
    TX
    MS-Off Ver
    Excel 2007
    Posts
    4

    macro to search text & return values

    Hello,

    I have a spreadsheet containing several names. I want a formula or macro that will search a string of text within the cell. If the text exists within the cell, I need a code to populate. For example,

    In cell E2, search for a phrase anywhere in the cell that contains "COM". In cell H2 look for the exact phrase "ASB". When "COM" is found in E2 and "ASB" is found in H2, in cell U2 return the value "COM ASBTVL".

    In my case, E2 may not contain "COM", it may contain "DEN" instead. In that case, if E2 contains "COM", and if "H2" contains "SVC", then in U2 populate "COM APPSVC"

    I'm guessing in my code I will need to list a set of criteria that will search row by row and return certain values when finding the text I specify.


    Would a macro be best for this? Does anyone know how I would put that together?
    Last edited by leni; 01-09-2009 at 04:05 PM. Reason: Brought font to acceptable level

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492
    =IF(AND(SEARCH("COM",E2)>0,SEARCH("ASB",H2)>0),"COM APPSVC","")

    Now use that as a basis. Instead of hardcoding "COM" into the formula, you could reference another cell to give it flexibility.

    The same is true for the "COM APPSVC", you could have that result come from another cell as well.

    Will that get you started?
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    01-09-2009
    Location
    TX
    MS-Off Ver
    Excel 2007
    Posts
    4
    Dear JBeaucaire:

    Thank you so much for your post. This is definitely much closer to what I want to accomplish than I was before. I agree with your suggestion - to have the "COM APPSVC" result from another cell.

    I am thinking that I may need several of these statements below, all with different criteria, to finally return the code. Perhaps this could be accomplished by a list of criteria inside a VBA macro?

    =IF(AND(SEARCH("WAR",E2)>0,SEARCH("TB",H2)>0),"WAR APPTVL","")
    =IF(AND(SEARCH("CRD",E2)>0,SEARCH("TB",H2)>0),"CRD APPTVL","")
    =IF(AND(SEARCH("ABS",E2)>0,SEARCH("TB",H2)>0),"ABS APPTVL","")
    =IF(AND(SEARCH("FHW",E2)>0,SEARCH("TB",H2)>0),"FHW APPTVL","")

    Thanks in advance for your contributions,

    Leni

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    You could do it like this:
    Please Login or Register  to view this content.
    The formula in E4 is =INDEX(C1:C4, INDEX(MATCH(E2&H2, A1:A4&B1:B4, 0), 0))

    It could be simplified by the fact that the second word is always "TB", but the approach allows it to be different.
    Entia non sunt multiplicanda sine necessitate

  5. #5
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492
    SHG, don't forget the SEARCH function needs to be active in there, too. The text strings have to be searched to find those particular text strings.

  6. #6
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    I did forget, JB -- never mind

  7. #7
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492
    It appears your searches ALL require H2 to have TB in the text string, so you can parse that out first. Then you can search for up to 6 more things before needing a different approach since IF statements can go 7 deep. Initially, something like this works:

    =IF(ISERROR(SEARCH("TB",H2)),"No TB",IF(ISERROR(SEARCH("WAR",E2)),IF(ISERROR(SEARCH("CRD",E2)),IF(ISERROR(SEARCH("ABS",E2)),IF(ISERROR(SEARCH("FHW",E2)),"","FHW APPTVL"),"ABS APPTVL"),"CRD APPTVL"),"WAR APPTVL"))

    You can change the "No TB" in that formula to just "". You can add two more IF levels to that formula if needed.
    Last edited by JBeaucaire; 01-09-2009 at 07:57 PM. Reason: Took out unnecessary >0 references

  8. #8
    Registered User
    Join Date
    01-09-2009
    Location
    TX
    MS-Off Ver
    Excel 2007
    Posts
    4
    What if I have more than 7 sets of criteria?

  9. #9
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492
    When you come back, go ahead and have the full set of needs ready to go. Too often a "simplified" example gets worked and worked until a solution is found, only to discover it won't scale up to the real need. Start with the real need.

  10. #10
    Registered User
    Join Date
    01-09-2009
    Location
    TX
    MS-Off Ver
    Excel 2007
    Posts
    4
    Sorry JB, I typed out an illustration for you and have attached it.

    The reason I want to search in Column A for only part of the text is sometimes the data in Column A may be mispelled.

    Anytime the code in Column B is ASNB, NSNB or TNB, I will not need anything populated in Column C.

    I hope this helps.

    Thanks again,
    leni
    Attached Files Attached Files

  11. #11
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492
    So, in reality, you just need to search the CODE column for a match to a set of codes, if one is found, then return a matching code along with the abbreviation for the state? Are there any other requirements?

    So far, the code options I see are:

    ASB - - - APPSVC
    NSB - - - NETSVC
    TBA - - - APPTVL
    TBN - - - NETTVL

    Any more?

    And you get misspellings of the states? Couldn't you restrict the input to only actual State names? Are those cells manually entered?

  12. #12
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492
    Several suggestion. All incorporated in this sample.

    Use Conditional Formatting in column A to highlight misspelled states so they get corrected immediately.

    I've added a table to lookup the code and the matching results, and a STATES table.

    The formulas in column C incorporate everything and pull together the state abbreviation with the service code.

    There are several discrepancies in your sample data, where the same code had different results. I color coded them to point them out and how they differ from my results. You'll need to make sure those discrepancies really WERE discrepancies.

    Look it over.
    Attached Files Attached Files
    Last edited by JBeaucaire; 01-10-2009 at 04:53 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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