How do you conditionally format arbitrary text in google sheets?-CodePudding

How do you conditionally color a column where the column contains Alphanumeric text, such that all like strings are colored the same? Aware how to do this if you know the value of possible strings, but in this case combinations are not known ahead.

Thought about converting string=>hex=>binary=>decimal, then do a color scale format rule. Then realized these functions are readily available without custom functions.

CodePudding user response：

Color scale formatting can only format numbers. Assuming that the text strings are in column A2:A, you can convert duplicates among them into fairly unique numbers with this formula in row 2 of a free column:

=arrayformula( 
  lambda( 
    data, modulo, 
    map( 
      to_text(data), 
      lambda( 
        text, 
        if( 
          countif(data, text) < 2, 
          iferror(1/0), 
          iferror( 
            mod( 
              sum( 
                mod( 
                  1.1 ^ sequence(len(text)) 
                  * 
                  code( mid(text, sequence(len(text)), 1) ), 
                  modulo 
                ) 
              ), 
              modulo 
            ) 
          ) 
        ) 
      ) 
    ) 
  )( A2:A, 8 ) 
)

Then format the new column as a color scale and use the values there as a guide to locate duplicate text strings in column A2:A.

CodePudding user response：

You can try using the following formula:

=IF(REGEXMATCH(A1, "[^A-Za-z0-9] "), FALSE, TRUE)

This is an example of how it works:

Once you have the formula you can use conditional formatting in Google Sheets to change the cell color to the one you prefer.

References: