Bold part of text in a cell, based on contents of separate column

as far as I know, there is no way to do cell formatting on partial contents of a cell.

conditional formatting will only apply to the entire cells contents.

Also the comments cannot be in the same cell as a formula. its one or the other.

Jason

I attempted that but it then missed the term if it is at the beginning or ending of the sentence or if it has punctuation after it. REGEX I think solves that.

=TEXTBEFORE(C2,REGEX("\b("&B2&")\b",FALSE))&B2&TEXTAFTER(C2,REGEX("\b("&B2&")\b",FALSE))

Notice row 3 only highlights the first "cat" in the sentence. I'm still stumped on how to do more. It also removes the capitalization because the word being inserted is not capitalized.

An alternative solution that gets all occurrences and has no problem with capitalization is to capitalize each occurrence instead of using bold. SUBSTITUTE can do that easily.

C2 =SUBSTITUTE(B2,REGEX("\b"&A2&"\b",0),UPPER(A2))

I have a way to do it if there is only one occurrence of the term in the sentence. It is probably possible to do additional occurrences but it might take a lot more work to do it. Is one enough?

Column B formula is =A

Format these cells as bold

Formula in D is =TEXTBEFORE(C,B)&B&TEXTAFTER(C,B)

You can do without column B if you bold column A instead and substitute A for the B's in the formula

It is unlikely that this will export to Excel and work the same way, if that matters.

There is one problem with this particular formula. If the sentence has a word that contains the term (such as "cat" is in "catamaran"), it might get incorrectly highlighted there (catamaran). I'll have to think about how to fix that.

Clever solution!

Regarding "I'll have to think about how to fix that."

This may do the fix:

Formula is the same as in Badunit's post. The only changes are to enter/type a space before and after cat in cell, B2, and to apply bold to that entry. If these changes were made to the entry in column A, there would be no need to repeat the entry in column B.

Regards,

Barry