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?

Use either of the 2 free filehosts below to upload a sample of your actuals
(Desensitize it first, if needed). Then copy & paste the generated link to
your sample file in response he

http://www.flypicture.com/
http://cjoint.com/index.php

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Actually I found my problem but dont really know how to correct it. I
was looking for "ENT" and it displays this when "statement" is in the
cell even though i used "FIND" and "ENT" by itself is before statement
in the cell. is there a way i can correct this so it searchs from
left to right and stops once it finds it or something else i need to
do? Also, when it doesnt find a match it displays "NA" is there a way
i can keep it blank if it doesnt find anything?

"natek1234" wrote
Actually I found my problem but dont really know how to correct it. I
was looking for "ENT" and it displays this when "statement" is in the
cell even though i used "FIND" and "ENT" by itself is before statement
in the cell.

Think you meant the upper case "STATEMENT"

Is there a way i can correct this so it searchs from left to right
and stops once it finds it or something else i need to do?

The search is already from left to right.

One way which may suffice here is to use a leading space for "ENT",
ie input it to search for string: <spaceENT
within the reference search list in E2:E6
instead of just: ENT

This will avoid it picking up STATEMENT
and you could just use TRIM on the return in col B

Also, when it doesnt find a match it displays "NA" is there a way
i can keep it blank if it doesnt find anything?

Yes, of course. Just add a front error trap: IF(ISNA(MATCH...),"",

With the error trap and TRIM bolted on,
try instead in B2, array-entered:
=IF(ISNA(MATCH(TRUE,ISNUMBER(FIND(E$2:E$6,A6)),0)) ,"",TRIM(INDEX(E$2:E$6,MATCH(TRUE,ISNUMBER(FIND(E$ 2:E$6,A6)),0))))
Copy B2 down as far as required
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Errata to earlier formula (pasted the formula for B6, instead of B2)
.. try instead in B2, array-entered:
=IF(ISNA(MATCH(TRUE,ISNUMBER(FIND(E$2:E$6,A2)),0)) ,"",TRIM(INDEX(E$2:E$6,MATCH(TRUE,ISNUMBER(FIND(E$ 2:E$6,A2)),0))))

The above presumes that the strings searched for are always "embedded"
within the source cells with a leading space (as per your original post's
sample).

If that may not always be the case, just amend the source by inserting a
leading space as the 1st character in all source cells.
In an empty col, say in H2: =" "&A2
Copy H2 down, then copy col H and overwrite col A
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---