+ Reply to Thread
Results 1 to 3 of 3

Find partial text in range based on another range of shortened values

  1. #1
    Registered User
    Join Date
    04-10-2019
    Location
    VA
    MS-Off Ver
    Excel 2010
    Posts
    23

    Question Find partial text in range based on another range of shortened values

    So what this is, is a list of transactions in Bank speak. And from those transactions I've taken common words that appear in them no matter what the variation. From there they are assign categories

    Ex. bank speak = AMAZON.COM*1T9SS2M AMZN.COM/BILLWA (everything after the * may change, but the beginning is always amazon.com)
    bank speak = AMZN MKTP US*03692 AMZN.COM/BILLWA (AMZN MKTP is always there)
    These would both fall under the Amazon Category

    Now what I want to do is have the bank spoken transactions go through their shortened version (amazon.com, AMZN MKTP) and have their category assign accordingly (Amazon). I know I could do this as a formula with a nested if, but that would be a big nested if, and I would really rather not.

    I've tried using functions like Vlookup and INDEX/Match, but they would require me to reference the shortened versions to assign categories which doesn't work for how this is set up. As you'll see in the attached file.

    I'm not asking ya'll to write my code, but if you could give me some reference or point me in the right direction. I know there is some way to do this in VBA.
    Also I'm not an expert on VBA so some explanation may be required if you do supply code examples, just FYI.

    Thank you,
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    2,102

    Re: Find partial text in range based on another range of shortened values

    This code can do the trick:

    Please Login or Register  to view this content.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    04-10-2019
    Location
    VA
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: Find partial text in range based on another range of shortened values

    Yes, thank you. It worked like a charm.

+ 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] Trying to Find Average of Values Based on Lookup Range
    By brafter70 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-13-2018, 02:17 PM
  2. [SOLVED] Find exact partial string in range
    By sh1msh0n in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-01-2017, 10:25 AM
  3. Find and display certain text in a string based on a range of text.
    By JonesyCC in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-05-2017, 02:18 PM
  4. Find partial text in a range o cells
    By bbb13 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-03-2015, 02:20 PM
  5. Replies: 2
    Last Post: 09-07-2013, 07:50 PM
  6. [SOLVED] Help using VBA to highlight a partial column based on text in a cell out of the range
    By JTKEXCEL in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 04-20-2013, 04:46 AM
  7. find values in a range based on criteria
    By Sreejith.ag in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-31-2012, 08:12 AM

Tags for this Thread

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