Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    May 2001
    Location
    Wichita, Kansas, USA
    Posts
    177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    'If' forumla looking for text (Excel 2000)

    I have oftentime used If statement in the past, but always with numerical data. Now I have a statement I'm trying to write, which will refer a text cell, and it isn't working right.
    If I have the word "solo" in a cell, for instance. In another cell, I can have the formula =IF(D1="solo",0,1) and it will properly return a 0 for the word Solo. But I want it to return a true value even if the cell doesn't just say "Solo" -- but says "Solo 1200" or "Solo 3900", etc etc. I have tried an asteriks after the word Solo, and that just keeps it from functioning entirely. So how do I tell it to return a true value if the letters "solo" are ANYWHERE in the cell? THanks!

    Tracy

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Flims, Switzerland
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'If' forumla looking for text (Excel 2000)

    This should work:-

    =IF(ISERR(SEARCH("solo",D1)),1,0)


    This however, will return 0 for a value like "1solo2". If you want it to return 0 for values only where "solo" or "Solo" is a seperate word, then let me know, and I'll come up with a revised formula.

  3. #3
    2 Star Lounger
    Join Date
    May 2001
    Location
    Wichita, Kansas, USA
    Posts
    177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'If' formula looking for text (Excel 2000)

    Adam
    That worked perfectly! THANK YOU! It didn't matter Where in the cell the text string was located -- so your solution worked great.

    I have not worked much with the IS functions before, so I will research this ISERR for future use!

    Tracy

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Flims, Switzerland
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'If' formula looking for text (Excel 2000)

    Your welcome!

    They key to this formula is the SEARCH() function. This searches for the occurence of once piece of text in another.

    The ISERR() function is used, because if the SEARCH() function cannot find the text, it returns #VALUE!, which is an error. The ISERR() function returns TRUE if it is passed an error value, like #VALUE!, and returns FALSE if it is passed a non-error value.

    So, if SEARCH() finds the text "solo", then it returns the position it was found at, if it doesn't find it, it returns an error. The ISERR() function then converts this to a TRUE or FALSE, and the IF() function then converts that to your 0 or 1.

    (Sorry if that explanation was a little too verbose - I'm not sure what your level of excel function experience is.)

  5. #5
    New Lounger
    Join Date
    May 2001
    Location
    Santa Fe, New Mexico, USA
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'If' formula looking for text (Excel 2000)

    I'm wondering if this function could work for what I need. We have a clunky software that queries the AS-400. Very, very basic. Most of the time I copy and paste the results to Excel and manipulate from there.

    To my extreme dismay, the account's name and account number are concatenated. (Let's not get into why, because I don't think any one knows the real answer) Thus, when I want to use the account number for a VLOOKUP, I have to manually type the number in an adjacent cell. The string is text with the account number in parentheses ex : Your Place (90050). The account number is anywhere from 3 to 7 digits. We have over 36,000 accounts. Any ideas? This could change my life. <img src=/S/crossfingers.gif border=0 alt=crossfingers width=17 height=16>

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'If' formula looking for text (Excel 2000)

    I would split the name and account number into separate cells. If the current account name and number are in column A, then put the following two formulas into the top cell of two empty columns:

    <pre>=TRIM(LEFT(A1,FIND("(",A1)-1))
    =MID(A1,FIND("(",A1)+1,LEN(A1)-FIND("(",A1)-1)
    </pre>


    The first one should extract the account name and the second one should extract the account number. Now, copy these two formula down the two columns.

    To get the Account names and numbers instead of the formulas, use the following procedure:

    1- Select the two columns with the formulas.

    2- Click on Copy on the Edit Menu.

    3- With the two columns still selected, click on Paste Special on the Edit menu.

    4- In the top section of the dialog box labeled Paste, click on the Values radio button.

    5- Click on OK.

    You can now delete the original column with the combined Account Name and Number if you want to.
    Legare Coleman

  7. #7
    New Lounger
    Join Date
    May 2001
    Location
    Santa Fe, New Mexico, USA
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'If' formula looking for text (Excel 2000)

    BRILLIANT! Works like a charm...

    If only this company was giving raises this year ! <img src=/S/woof.gif border=0 alt=woof width=15 height=15>

    At least I have the satisfaction of a job well-done AND impressing people who don't have the wisdom to hang out at Woody's Lounge.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •