4

Excel noobie here.

Does anybody know how I could perform the following action ?

In a column, whenever a text string from a cell contains more than x characters, I want it to be highlit through conditonnal formating. The cells look like that content-wise :

"cat|bird bird|dog dog dog"

I'd like cells to be highlit whenever one of the text strings contains more than x characters (a text string in this case being the text separated by a "|" from the other texts in the cell).

I'm pretty sure I need to do something with the LEN function but I don't know how exactly since I need the LEN to apply to every text separated by the "|".

I hope the explanation was precise enough.

Improve this question
0

2 Answers 2

Reset to default
8

If I understand your question correctly then, try below solution in case your version of Excel Supports FILTERXML function. While you did not define value of x, in this example I am setting it to 6.

See the below screenshot. Column D is only for reference. It's not used in formula.

Sample data is in Cells C2 to C9

Select Cell C2, go to Conditional Formatting and add a new rule. Put the following formula in it.

=MAX(LEN(TRANSPOSE((FILTERXML("<t><s>"&SUBSTITUTE(C2,"|","x</s><s>")&"x</s></t>","//s"))))-1) > 6

Select the formatting of your choice and save the rule. Now use format painter and apply the formatting to all of the intended cells.

FILTERXML creates an Array split by | and MAX & LEN work on the same as an Array when TRANSPOSE is used. The "x" is appended just in case you have all zeros as a string which will be evaluated to number 0 returning Length 1 instead of correct length.

Check this out and let me know if it works for you.

enter image description here

enter image description here

Improve this answer
4
  • Thanks for the response. Right now the formula seems to not work as seen on the screenshot : link I tried turning the " > 6" without space in ">6" and it still doesn't work, so I guess it's a "," problem that needs to be replaced with a ";" ? I also tried putting the formulas in D2 without the ">6" part and it doesn't work either link
    – FlorianWS
    Oct 3, 2021 at 5:28
  • Have you tried replacing the , in the formula with your applicable List Separator as set in Windows? Most likely it could be ; While Column D is not used anywhere, what error are you getting when you put the formula without >6 in a cell in column D? Does it say same as the screenshot in first link? OR does it return anything like #NUM or #VALUE or #NAME etc? What version of Excel do you have installed?
    – patkim
    Oct 3, 2021 at 5:38
  • List Separator in Windows - Control Panel --> Region --> Additional Settings --> List Separator
    – patkim
    Oct 3, 2021 at 5:46
  • It works when I replace the "," with ";" like this : =MAX(LEN(TRANSPOSE((FILTERXML("<t><s>"&SUBSTITUTE(H2;"|";"x</s><s>")&"x</s></t>";"//s"))))-1) " Everything is working like in your first screen, conditionnal formatting works perfectly as well ! Thank you so much for the help ! I was so impressed when you came up with this big formula as I couldn't find anything online ! Thanks again and have a great day ! Adeval
    – FlorianWS
    Oct 3, 2021 at 6:17
3

Do this:

  • Select the data range to highlight

  • Click Home > Conditional Formatting > New Rule

  • In the New Formatting Rule dialog, click "Use a formula to determine which cells to format"

  • enter the formula =LEN(A2)>15 into "Format values where this formula is true", where A2 is the first cell which contains the value, and >15 is the criteria to highlight cells

  • Click the Format button for the Format Cells dialog, and choose the color

  • Click OK > OK.

For more information with screenshots see How to highlight cells based on length of text in Excel?

Improve this answer
1
  • Thanks for your answer. Unfortunately, this will not work for what I'm trying to achieve as the LEN function will count the total number of characters in the cell whereas I'm trying to have a function count the number of characters of every "text strings" inside a cell (in my example "cat|bird bird|dog dog dog", I would like the function to count the characters for every text string, which would be 3 for cat, 9 for bird bird and 11 for dog dog dog. Then highlighting the cell if one of the text strings contains more than x characters.
    – FlorianWS
    Oct 2, 2021 at 18:08

You must log in to answer this question.

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