+ Reply to Thread
Results 1 to 2 of 2

Forumula That Will Pull A Spefic Word From A Cell

  1. #1
    Registered User
    Join Date
    02-07-2016
    Location
    Ireland
    MS-Off Ver
    2016
    Posts
    1

    Forumula That Will Pull A Spefic Word From A Cell

    Hi everyone, Ive been pulling my hair out over this for the past two hours & have resorted to making an account so I could query you guys directly. Hopefully I can get either a solution or new input into what I'm trying to do and an alternate solution.

    Any help is sincerely appreciated!


    I'm looking for a formula that will pull a word from a cell, based on a set of words, if it's present in that cell. If that makes sense? So lets say I've flagged words like "KFC", "Visa", "ATM" if any one of those words are present in a cell they will appear in the cell my formula is based in.

    What I'm trying to do & Why.
    I'm trying to create a personal expenditure report for my bank account. The idea I have is I can download an excel version of my statement from the bank & paste it into an excel workbook. This workbook will have a vlookup in it that will total up transactions based on their reference. So lets say I go to KFC twice a month the reference that appears on my bank statement will be POS KFC, so I can create a vlookup that will search for that reference and give me a total for the month that I've spent in KFC. I could do the same thing for ATM withdrawals, Groceries, my Rent, my Light & Heath, etc. and ultimately have an automatic report built of what I've spent my money on that month. The issue I'm having is with the referencing on my bank statement. The cell that contains reference for the store I've spent money in or bill m paying also has a date in it which will kill my vlookup. I have an example below of the reference that appears on my bank statement;

    ATM BRAY2 30/01 11:17
    POS KFC - CARRIC 27/01 1
    POS SUPERVALU 26/01 11:0
    POS MICROSOFT 25/01 09:4

    I was thinking I could run a formula connected to each of those cells that will search them for words like "ATM", "KFC", "Supervalu", "Microsoft" etc and if any one of them is present the word will appear next to the cell. I can then run the vlookup for those words and not have to worry about the date.

    Any ideas guys? I'm pulling my hair out. Using a formula like RIGHT or LEFT won't work because the words in the cells are different sizes.

  2. #2
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Forumula That Will Pull A Spefic Word From A Cell

    Maybe something like this...

    Create a list of keywords (col D).

    Data Range
    A
    B
    C
    D
    1
    ATM BRAY2 30/01 11:17
    Bray2
    ------
    KFC
    2
    POS KFC - CARRIC 27/01 1
    KFC
    Supervalu
    3
    POS SUPERVALU 26/01 11:0
    Supervalu
    Kmart
    4
    POS MICROSOFT 25/01 09:4
    Microsoft
    Walmart
    5
    Sears
    6
    Microsoft
    7
    Bray2


    This formula entered in B1 and copied down:

    =IFERROR(LOOKUP(1E100,SEARCH(D$1:D$7,A1),D$1:D$7),"")
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

+ 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] Pull first word if one word , pull first if two words
    By makinmomb in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-12-2015, 04:22 PM
  2. Replies: 0
    Last Post: 10-29-2015, 01:23 PM
  3. Replies: 5
    Last Post: 02-02-2015, 07:51 PM
  4. Pull spefic data, sort & average it and put into seperate tables
    By antmcg12 in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 11-06-2014, 01:18 PM
  5. Replies: 6
    Last Post: 03-12-2014, 12:16 PM
  6. a forumula to look up a worksheet and pull information to a summary page
    By cubeworker in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-04-2010, 08:43 AM
  7. [SOLVED] Counting spefic cell (not a range) with a value greater than 1
    By mmock in forum Excel General
    Replies: 5
    Last Post: 04-27-2005, 10:06 AM

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