‘If’ forumla looking for text (Excel 2000)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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:

=TRIM(LEFT(A1,FIND("(",A1)-1))
=MID(A1,FIND("(",A1)+1,LEN(A1)-FIND("(",A1)-1)

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.

Reply | Quote

BRILLIANT! Works like a charm…

If only this company was giving raises this year !

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.

Reply | Quote