| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 70698 | 2006-07-13 00:25:00 | XL Function | B.M. (505) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 470458 | 2006-07-13 00:25:00 | 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 |
B.M. (505) | ||
| 470459 | 2006-07-13 01:47:00 | 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. |
mejobloggs (264) | ||
| 470460 | 2006-07-13 05:03:00 | 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. |
RogerRamjet (7055) | ||
| 470461 | 2006-07-13 05:20:00 | Try this in cell B1 : =IF(ISERROR(SEARCH("76",A1)),0,1) HTH, Andrew |
andrew93 (249) | ||
| 470462 | 2006-07-13 21:50:00 | 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. |
B.M. (505) | ||
| 470463 | 2006-07-14 02:47:00 | 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 |
odyssey (4613) | ||
| 470464 | 2006-07-16 23:15:00 | 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: |
B.M. (505) | ||
| 470465 | 2006-07-19 03:18:00 | 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 ;) ) |
B.M. (505) | ||
| 470466 | 2006-07-19 04:02:00 | 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 |
SolMiester (139) | ||
| 470467 | 2006-07-19 04:52:00 | 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: |
B.M. (505) | ||
| 1 2 | |||||