+ Reply to Thread
Results 1 to 11 of 11

Extract a single number after a specific word that shows up in some cells and not others

  1. #1
    Registered User
    Join Date
    10-10-2012
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    4

    Extract a single number after a specific word that shows up in some cells and not others

    I have a list of information (A1 through A10):

    Jim:5 Rachael:2 Dan:5 Brock:5
    Rachael:2 Jim:5 Brock:2 Dan:2
    Jim:1 Rachael:5
    Rachael:5 Dan:1 Brock:2
    Rachael:3 Brock:1 Lord:1
    Rachael:2 Kim:1
    Rachael:2 Dan:4 Brock:4
    Rachael:2
    Rachael:4 Dan:2
    Rachael:3

    To the right of each cell, I would like to see the number that appears after "Dan:". If Dan does not appear in the cell, I would like the cell to display a 0.

    So in this example, my results would be (B1 through B10):
    5
    2
    0
    1
    0
    0
    4
    0
    2
    0

    After that is working, the idea would be to be able to take this formula and modify it by replacing the word "Dan" with any other word (always ending in a colon) (like Rachael or Brock) to similarly create a column of the appropriate numbers.

    I've read almost all of the "extract info from cells and place into other cell" threads. None of them quite hit on my issue or I could not understand them. The one that came the closest was this: http://www.excelforum.com/excel-form...g-numbers.html -- but I could not get it to work for my situation quite right.

    Any assistance would be greatly appreciated. Thank you for reading.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Extract a single number after a specific word that shows up in some cells and not othe

    try this...
    =IF(ISERROR(SEARCH("dan",B1,1)),0,RIGHT(B1,1))
    if you need the answer t be a value, instead of text, use this...
    =IF(ISERROR(SEARCH("dan",B1,1)),0,VALUE(RIGHT(B1,1)))

    to extract any name, put the name in, say, E1 and use this...
    =IF(ISERROR(SEARCH($E$1,B1,1)),0,VALUE(MID(B1,SEARCH($E$1,B1,1)+LEN($E$1),1)))
    Last edited by FDibbins; 10-10-2012 at 11:42 PM.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,845

    Re: Extract a single number after a specific word that shows up in some cells and not othe

    Let me know if this works for you.

    - Vince
    Attached Files Attached Files

  4. #4
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,845

    Re: Extract a single number after a specific word that shows up in some cells and not othe

    Quote Originally Posted by FDibbins View Post
    try this...

    to extract any name, put the name in, say, E1 and use this...
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    I think you forgot a '+1' after the 'LEN($E$1)' to account for the ":" after the name.

    - Vince

  5. #5
    Registered User
    Join Date
    10-10-2012
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Extract a single number after a specific word that shows up in some cells and not othe

    Thank you folks. Highly brilliant. Works like a charm. Gods among men.

  6. #6
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,845

    Re: Extract a single number after a specific word that shows up in some cells and not othe

    You're welcome.

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Extract a single number after a specific word that shows up in some cells and not othe

    you are correct Moo, in my test, i didnt add the ":", thanks

    @ trovis you'r welcome. If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neat and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below)

  8. #8
    Registered User
    Join Date
    10-10-2012
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Extract a single number after a specific word that shows up in some cells and not othe

    Thanks for telling me about that. I am new here. Reps all around.

    Before I mark as solved, I wonder if you have any thoughts on an issue I'm running into. It's a minor nuisance, but it could pose a problem.

    Later in my dataset, I have some entries that might say, for example: "Rachael:4 Steve:1 Dana:3 Dan:2" If I am using "Dan" as my search term, it returns an error next to that entry.

    On the other hand, if the entry said: "Dan:2 Rachael:4 Steve:1 Dana:3" (the "Dan" is before the "Dana"), then it will not return an error.

    For all I know, this would be very difficult to fix. But if you have a simple fix, I'd love to hear it.

  9. #9
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,845

    Re: Extract a single number after a specific word that shows up in some cells and not othe

    That one is easy.... in the workbook I attached earlier, change "Dan" in cell C1 to "Dan:"
    Then you also have to REMOVE the +1 after the LEN($C$1) part.

    - Vince

    If you make that change, from then on you will have to search for Dan: or Brock:

    You will have to include the : in your search string in cell C1

  10. #10
    Registered User
    Join Date
    10-10-2012
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Extract a single number after a specific word that shows up in some cells and not othe

    I'd rep you again if I could, Moo. Thanks so much. Solved.

  11. #11
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,845

    Re: Extract a single number after a specific word that shows up in some cells and not othe

    You bet

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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