find certain text and display it

  • Thread starter Thread starter natek1234
  • Start date Start date
N

natek1234

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.
 
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.
Click to expand...


---
 
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
 
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?
 
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 said:
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.
Click to expand...

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?
Click to expand...

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: <space>ENT
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?
Click to expand...

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

---
 
Errata to earlier formula (pasted the formula for B6, instead of B2)
.. try instead in B2, array-entered:
Click to expand...
=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

---
 
Back
Top