Search for a particular letter in the sentence and print

[ks1]

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

 

[Per Jessen]

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

 

[Steve Dunn]

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.

 

[Steve Dunn]

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?

 

[Jacob Skaria]

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)