# Formula to return a word from string of text

1. ## Formula to return a word from string of text

Hi,

In the attached workbook i have a formula that will say, if col a is yes, lookup col b in a required fields mapping and if the relevant column has 'X' in it then return column c.

I have added a hard coded version of what i need to pull from the string of text and what the formula currently pulls in.

In the formula returned cell i need it to identify weather the string of text in col c has either "CALL" or "PUT", which ever one it has it needs to return it (Call or PUT)

Any ideas how i can add this to the formula?

2. ## Re: Formula to return a word from string of text

If A1 contains some text and we want a formula to return CALL if the text contains CALL and we want the formula to return PUT if the text contains PUT, then:

=IF(ISNUMBER(FIND("PUT",A1)),"PUT",IF(ISNUMBER(FIND("CALL",A1)),"CALL",""))

3. ## Re: Formula to return a word from string of text

For chuckles....

=LOOKUP(2, 1/(ISNUMBER(SEARCH({"CALL","PUT"},C2))), {"CALL","PUT"})

4. ## Re: Formula to return a word from string of text

no need for isnumber there and may want to handle the error if no match

=IFERROR(LOOKUP(2, 1/(SEARCH({"CALL","PUT"},C2)), {"CALL","PUT"}),"")

;-)

5. ## Re: Formula to return a word from string of text

Thanks & regards

If there is no match i just want the cell to remain blank, would i need to just add ,"" to the add of the code?

6. ## Re: Formula to return a word from string of text

two of the three posted versions already do that ;-)

7. ## Re: Formula to return a word from string of text

Many thanks.

I have a similar situation with the same string of text, i need to return the value at the end of the text.

The problem is the value will be different on each line, the only thing that will remain the same is that the value will follow a date.

I have done an example attached.

8. ## Re: Formula to return a word from string of text

As you requested:

9. ## Re: Formula to return a word from string of text

Many thanks, how exactly does this work?

10. ## Re: Formula to return a word from string of text

It creates an internal array (using an artificial character) and then gets the last element of that array.

11. ## Re: Formula to return a word from string of text

I've added this another formula and this is the final formula but it just returns 'FALSE'

``Please Login or Register  to view this content.``

Thanks.

13. ## Re: Formula to return a word from string of text

Following some excellent advice above i am now using the formula;

``Please Login or Register  to view this content.``
But i need to add in that i only want this to return values when cell AI = "yes" & cell AK - "EIO".

How would i add this in?

14. ## Re: Formula to return a word from string of text

Add it at the beginning in IF/AND tests and your old formula becomes the TRUE result.

=IF(tests, formulaifTRUE, formulaifFALSE)

=IF(AND(AI2="yes", AK2 = "EIO"), --MID(A2,FIND(CHAR(1),SUBSTITUTE(A2," ",CHAR(1),LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))+1,255), "")

===========

15. ## Re: Formula to return a word from string of text

Brilliant, thank you

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

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