Hey All, I am looking for an excel formula that can look into a cell
and find certain text and display it. an example may be more useful,
i have a cell with data like this in coulmns "(11, 1 DATA, ABC, 22)
DATA" and (22, 2 DATA, DEF, 33) DATA" and i need to be able to search
both those cells for "ABC" and "DEF" and if it appears, display it in
a different cell. i know how this can be done for just one string but
i will need to look for 5 different ones.

One way

Assume you have the 5 strings listed in E2:E6
(eg: ABC, DEF, ...)

and the source data is running in A2 down
(eg: (11, 1 DATA, ABC, 22)DATA, etc)

Put in B2, then array-enter the formula by pressing CTRL+SHIFT+ENTER:
=INDEX(E$2:E$6,MATCH(TRUE,ISNUMBER(SEARCH(E$2:E$6, A2)),0))
Copy B2 down as far as required to return the results: ABC, DEF, etc,
depending on which string is present within the source data. It's presumed
that only one of the 5 strings in E2:E6 would be present in each source data
cell, if at all.

Replace SEARCH with FIND if you want the expression's search to be case
sensitive. FIND is case sensitive.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"natek1234" wrote in message
oups.com...
Hey All, I am looking for an excel formula that can look into a cell
and find certain text and display it. an example may be more useful,
i have a cell with data like this in coulmns "(11, 1 DATA, ABC, 22)
DATA" and (22, 2 DATA, DEF, 33) DATA" and i need to be able to search
both those cells for "ABC" and "DEF" and if it appears, display it in
a different cell. i know how this can be done for just one string but
i will need to look for 5 different ones.

i tried that but all i am getting in B2 is "ABC" ; any thoughts?

But isn't that what you wanted displayed?

Re your line:
.. i need to be able to search both those cells
for "ABC" and "DEF" and if it appears,
display it in a different cell.

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"natek1234" wrote in message
ups.com...
i tried that but all i am getting in B2 is "ABC" ; any thoughts?

Kindly keep discussions within the newsgroup thread for the benefit of all.

I don't know what's possibly happening over there, but this sample
illustrates the earlier suggestion in working order:

http://cjoint.com/?jzbkFUYKn1
natek1234.xls
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

--- natek1234 wrote:

Actually, I need it to just find certain text and
display that text,
my desired results would look something like this
with * around what i
need a formula for:

*"ABC" "(11, 15 DATA, ABC, 22)
DATA1"
*"DEF" "(22, 23 DATA, DEF, 33)
DATA2"
*"CBA" "(11, 11 DATA, CBA, 22)
DATA3"
*"BAC" "(22, 44 DATA, BAC, 33)
DATA4"

your formula above makes these all "ABC" but maybe
im doing something
wrong

Your examples helps out a lot and is really nicely done. However,
when I try to implement the same thing in my spreadsheet I get mixed
results again. For the most part it works, but for some reason it
thinks it matches some data that doesnt really exist. And if i move
around the data i have it looking for some of the results will change
and i dont know why. i tried to break your example so i can explain
it better but yours is not acting the same way. should the data its
looking for be sorted in anyway? or do you have any ideas? would you
like me to send you mine?