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

1. ## 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. ## 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)))

3. ## 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

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

Originally Posted by FDibbins
try this...

to extract any name, put the name in, say, E1 and use this...
Formula:
`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. ## 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. ## Re: Extract a single number after a specific word that shows up in some cells and not othe

You're welcome.

7. ## 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. ## 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. ## 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. ## 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. ## Re: Extract a single number after a specific word that shows up in some cells and not othe

You bet

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