Search for a particular letter in the sentence and print

K

ks1

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

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
 
S

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.
 
S

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?
 
J

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top