If a cell contains a specific word, return pre defined string for that word.

[slamont]

Hi All,

I'm a bit stumped.

What I would like to do is if cell B:B contains the string "one" return "1" in column F or if contains "two" return "2" or "three" return 3 and so on and so on.

This may have up to 20 specific searches in the one cell.

Hope this makes sense and many thanx in advance.

Shane

[slamont]

sorry...also the formula shown works for 2x searches but can't seem to add more

[Pete_UK]

You could set up a small table with the words one, two, three etc. in column M, say, and the corresponding numbers in column N, and then you could use this array* formula in F2:

=IFERROR(SUMPRODUCT(IF(ISNUMBER(SEARCH($M$1:$M$7,B2)),$N$1:$N$7,0)),"")

*Note that an array formula needs to be committed using the key combination of Ctrl-Shift-Enter (CSE), rather than the usual Enter.

Copy down as required.

Hope this helps.

Pete

[slamont]

Thanx for the reply, modified due to headers {=IFERROR(SUMPRODUCT(IF(ISNUMBER(SEARCH($M$2:$M$5,B2)),$N$2:$N$5,0)),"")} but returns 0

The return is a text string also if that changes anything. What is the ,0 after $N$5 ???

[Pete_UK]

The zero is for words which do not occur in the text. If none of the words are present, then you could use this variation to return a blank:

=IFERROR(1/(1/SUMPRODUCT(IF(ISNUMBER(SEARCH($M$2:$M$8,B2)),$N$2:$N$8,0))),"")

Note, this is still an array formula, so you must use CSE as previously advised.

My formula returns numbers, but if you want those to be treated as text values you can just add &"" at the end (still use CSE to commit the formula).

Copy down as required.

Pete

[slamont]

Is this what it should be?

{=IFERROR(1/(1/SUMPRODUCT(IF(ISNUMBER(SEARCH($M$2:$M$5,B2)),$N$2:$N$5,0))),""&"")}

Returns blank even though B2 contains the string

[slamont]

Hi All,

This one is still not solved. Any help greatly appreciated.

[Gregb11]

It works for me. Maybe you should attach your latest one that is not working and we can look at it.

[slamont]

thanx for the reply, please find file attached with the result in column F

[bebo021999]

MAy be in F2:

Please Login or Register  to view this content.

[slamont]

many thanx ...that worked on a the test spreadsheet

[bakedziti]

Is there a way to return this in text? So if the strong contains the word one, it should say 'One' and not '1'?

The exact details of what I am looking for is posted at https://www.excelforum.com/excel-for...ml#post5653350. But this solution would work if I can make it text instead of a number.

[nick.williams]

Try:

=IFERROR(INDEX($N$2:$N$8,MATCH(1,--ISNUMBER(SEARCH($M$2:$M$8,B2)),0)),"")

Note that if there are multiple occurrences, it will only match the first one in the list. e.g. if you have "one two" it will return the lookup for one, but not two. If would be a bit more complicated to deal with that.

[bakedziti]

Thank you for your respionse

[Pete_UK]

@bakedziti,

This does not appear to be your thread, as the OP had a username of slamont.

You should not ask a question in another user's thread - that is referred to as hijacking, and is covered in Rule 04 of the Forum Rules at the top of the page. Instead, you should start your own thread and link back to this one if you think it is relevant.

Pete