+ Reply to Thread
Results 1 to 15 of 15

Formula to return a word from string of text

  1. #1
    Forum Contributor
    Join Date
    05-01-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    822

    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?
    Attached Files Attached Files

  2. #2
    Forum Expert Jakobshavn's Avatar
    Join Date
    08-17-2012
    Location
    Lakehurst, NJ, USA
    MS-Off Ver
    Excel 2007
    Posts
    1,970

    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",""))
    Gary's Student

  3. #3
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Formula to return a word from string of text

    For chuckles....

    =LOOKUP(2, 1/(ISNUMBER(SEARCH({"CALL","PUT"},C2))), {"CALL","PUT"})
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  4. #4
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    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"}),"")

    ;-)
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  5. #5
    Forum Contributor
    Join Date
    05-01-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    822

    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. #6
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Formula to return a word from string of text

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

  7. #7
    Forum Contributor
    Join Date
    05-01-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    822

    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.
    Attached Files Attached Files

  8. #8
    Forum Expert Jakobshavn's Avatar
    Join Date
    08-17-2012
    Location
    Lakehurst, NJ, USA
    MS-Off Ver
    Excel 2007
    Posts
    1,970

    Re: Formula to return a word from string of text

    As you requested:
    Attached Files Attached Files

  9. #9
    Forum Contributor
    Join Date
    05-01-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    822

    Re: Formula to return a word from string of text

    Many thanks, how exactly does this work?
    Last edited by kenadams378; 06-17-2013 at 08:58 AM.

  10. #10
    Forum Expert Jakobshavn's Avatar
    Join Date
    08-17-2012
    Location
    Lakehurst, NJ, USA
    MS-Off Ver
    Excel 2007
    Posts
    1,970

    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.
    Last edited by Jakobshavn; 06-17-2013 at 09:23 AM.

  11. #11
    Forum Contributor
    Join Date
    05-01-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    822

    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.

  12. #12
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Formula to return a word from string of text

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
    Thanks.

  13. #13
    Forum Contributor
    Join Date
    05-01-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    822

    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. #14
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    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), "")

    ===========

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

  15. #15
    Forum Contributor
    Join Date
    05-01-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    822

    Re: Formula to return a word from string of text

    Brilliant, thank you

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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