Search for a particular letter in the sentence and print

  • Thread starter Thread starter ks1
  • Start date Start date
K

ks1

excel has to search for a paticular letter in a row and print the preeciding
word in the next cell
 
Hi

This formla search for '!' and return whatever is found after the '!' sign.

=IF(FIND("!",A1),MID(A1,FIND("!",A1)+1,99),"")

Hopes this helps
....
Per
 
Hi, I'm a little confused, do you have a row of cells with content such as:

A1:
testing x this y formula z

B1:
this p is q the r next s cell t along u

C1:
and a another b example c for d luck


and wish to search A1:C1, returning, for example, "the" when your criteria
letter is "r"?

If not, perhaps you could clarify your requirements a little further.

Steve D.
 
Or, is it more like one cell containing a sentence.

A1:
perhaps this is could work

and you wish to return, for example, "is" when your criteria letter is "u"?

This could become very messy. If, for instance, your criteria letter
appeared twice in the sentence (not unheard of <grin>) what would you want
your result to be?
 
Try the below....which search for an exclamation in row1 range(A1:Z1) and
returns the cell value of the next cell...

=INDEX(A1:Z1,SUMPRODUCT((ISNUMBER(SEARCH("!",A1:Z1))*
COLUMN(A1:Z1)))+1)
 
Back
Top