-1

How do I get a specific text if there is a specific letter in the row. E.g. row A1:A6 contains a letter "c" in any cell. It return the Text "Catch" in column A7. and if there is "b" then it should return "Bold" in A7. I have tried REPLACE formula but it is of no use. I have also tried TEXT function. but could not get the result. Anyone can help?

3
  • 1
    Welcome to Super User. We are not a script writing service. We expect users to tell us what they have tried so far (including the code they are using) and where they're stuck so that we can help with specific problems. Questions that only ask for code are too broad and are likely to be put on hold or closed. Please read How do I ask a good question?.
    – DavidPostill
    Sep 26, 2015 at 9:00
  • I have edited my question. It is the best possible way i could describe my problem. Sep 26, 2015 at 12:07
  • Are you saying that you want to search A1:A6. If any one of those cells contains the letter "c", you want A7 to contain "Catch". If any one of those cells contains "b", you want A7 to contain "Bold". What goes in A7 if neither is found? What if both are found? Will cells in A1:A6 contain only a single letter, or are you looking for whether either letter occurs anywhere in other content?
    – fixer1234
    Sep 27, 2015 at 2:38

2 Answers 2

1

It seems you were probably closest with the TEXT function but just did not get it finished correctly.

In A7, try this standard formula¹,

=iferror(text(match("c", A1:A6, 0), "Catc\h"), iferror(text(match("b", A1:A6, 0), "\Bol\d"), ""))

There is a precedent hierarchy to this formula. If there is both a c and a b in A1:A6, the c will produce Catch first and the b will not produce bold.


¹ The backslashes are escape characters that force literal interpretation of the characters used in the format mask. Without them, h would try and become the hour and d would try to become the day. No sure exactly what B wants to become but it needs the backslash.

3
  • I figured out what the backslash is for next to the last character (not commonly used or well documented so you might want to explain in your answer), but why do you need it before the B in Bold? BTW, clever answer. I never would have thought of using this approach.
    – fixer1234
    Sep 27, 2015 at 3:01
  • I was guessing that Excel was using the text as fill characters and displaying the MATCH result in the rightmost place unless you forced that character. But it looks like you might be right about using reserved characters. The B worked for me without a backslash, so maybe you run into a problem only if the MATCH value makes sense for whatever B is reserved for, or it could be different versions of Excel (couldn't find any reference to it).
    – fixer1234
    Sep 27, 2015 at 3:35
  • 1
    @fixer1234 - Apparently bbbb is for a four digit Buddhist calendar year with bb as a two digit.
    – user385793
    Sep 27, 2015 at 9:55
0

This formula will detect the presence of any "c" in row #1:

=IF(ISERROR(MATCH("*c*",1:1,0)),"not found","found")

perhaps you can adapt it for your need.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .