View Full Version : XL Function

Does anybody know of an XL function that will return a number in a separate cell if certain text appear in another?

For example: If cell “A1” contains the text “Rating 76 today” then I want to return in cell “B1” 1 if 76 appears anywhere in the cell “A1”, otherwise 0.

I can’t use the "right" or "left" function because the 76 may appear anywhere in the middle. Like it may appear as “R 76 Today”.

Hope that makes sense? :illogical

mejobloggs

13-07-2006, 01:47 PM

I think what you might be looking for is VLOOKUP.

Have a look in Excel help, and read about it to see if it is what you want.

RogerRamjet

13-07-2006, 05:03 PM

If the text surrounding the number is not important, then there is an add-in at http://www.andrewsexceltips.com/ which will help.

If you right click on the cell/range, select 'Andrew's Utilities/Text/Remove text from selected cells'. This leaves only the numbers and then you can use an If/Then formula as required.

I can highly recommend this add-in to all Excel users as I have found it to be quite useful on many occasions. And it's free!

Cheers.

andrew93

13-07-2006, 05:20 PM

Try this in cell B1 :

=IF(ISERROR(SEARCH("76",A1)),0,1)

HTH, Andrew

That works great Andrew, but I’d like to take it a step further as follows but I can’t get it to accept. Maybe a comma or parenthesis problem, although I’ve tried a few variations. :o

=IF(or(ISERROR(SEARCH("76",A1),(iserror(search("Itm",a1),1,0)))

As you can see if cell A1 contains “76” OR “Itm” we get 1 otherwise 0.

odyssey

14-07-2006, 02:47 PM

This should work

=--(OR(IF(ISERROR(SEARCH("76",A1)),0,1),IF(ISERROR(SEARCH("itm",A1)),0,1)))

Note: the double negative at the front of the statement is to turn the True/False output from the OR statement to 0 or 1.

Cheers

Dave

Gee, that’s an interesting way to address the matter Odyssey.

I must have had a Cache not updating somewhere because I see you posted this on the 14th and I’ve only just found it.

Anyway, I’ll give it a try and get back with the results. :thumbs:

Well, that works great but when I prefix it with if A1 = Blank leave blank as follows I get FALSE returned. :confused:

=IF(A1="","")=--(OR(IF(ISERROR(SEARCH("76",A1)),0,1),IF(ISERROR(SEARCH("itm",A1)),0,1)))

Anyone any suggestions? :D (without being rude ;) )

SolMiester

19-07-2006, 04:02 PM

Well, that works great but when I prefix it with if A1 = Blank leave blank as follows I get FALSE returned. :confused:

=IF(A1="","")

Dont think there is enough options in the equation.... if (a1 = ?, opt 1, opt 2)

so

=IF(A1="","",=--(OR(IF(ISERROR(SEARCH("76",A1)),0,1),IF(ISERROR(SEARCH("itm",A1)),0,1))))

something like that anyway....Good luck

Nooooooop, still complains there's something wrong. I've underscored where the cursor's blinking.

=IF(A1="","",=--(OR(IF(ISERROR(SEARCH("76",A1)),0,1),IF(ISERROR(SEARCH("itm",A1)),0,1)))) :confused:

SolMiester

19-07-2006, 05:00 PM

Yes, = not required as is for start of equation, and we which to show argument

odyssey

23-07-2006, 08:25 PM

if you want to test whether the cell is blank use the IsBlank() function. Should come out something like:

=IF(ISBLANK(A1),"",--(OR(IF(ISERROR(SEARCH("76",A1)),0,1),IF(ISERROR(SEARCH("itm",A1)),0,1))))

Cheers

Dave

Powered by vBulletin® Version 4.2.5 Copyright © 2019 vBulletin Solutions Inc. All rights reserved.