I have this sheet with a bunch of Ls and Hs that come in 4 combinations.

I want Google Sheets to identify which pattern the Ls and Hs are.

Here are some examples of what the Ls and Hs look like:

HLLL
LH
LHLL
LHHL
LHHH
LHHHH

I want Google Sheets to be able to run a check like:

if cell contains LH, continue, else change cell text to "Pattern 1"

if cell contains HL, continue, else change cell text to "Pattern 2"

if contains LL, change cell text to "Pattern 3", else change cell text to "Pattern 4"

However, each set cannot contain 2 patterns. So HLLL cannot be Pattern 1 and Pattern 3 at the same time. It must be Pattern 1.

Is there any way to do this in Google Sheets?

Thanks.

EDIT**

I was able to color code the patterns with conditional formatting but I'm still unable to solve the original problem. Here's what my conditional formatting looks like:

Formula in B1:

=INDEX(IF(A1:A<>"",MATCH(REGEXREPLACE(A1:A,"^(?:(HL).*|(LH)H*|. ([HL]L))$","$1$2-$3"),{"HL-","LH-","-LL","-HL"},0),""))

The regular expressions ^(?:(HL).*|(LH)H*|. ([HL]L))$ means to match:

• ^ - Start string anchor;
• (?: - Open non-capture group to allow for alternations;
  • (HL).* - A 1st capture group to match the 1st pattern;
  • (LH)H* - A 2nd capture group to match the 2nd pattern;
  • . ([HL]L) - A 3rd capture group to match both the 3rd and 4th subpattern.
• )$ - Close non-capture group and match end-string anchor.

REGEXREPLACE() - will return any of the following patterns; {"HL-","LH-";"-HL";"-LL"} and MATCH() will return the appropriate number of any of those.

CodePudding user response：

Your description is somewhat inconsistent but I think this is what you are going for. The criteria of your conditional formats can be custom formulas and not just the rigid preset match types:

Read custom formulas as if you applied them to the top left cell of the formatted range. The references in the formulas are absolute column but relative row reference ($A2). The absolute column reference means both A2 and A3 will look at A2 to determine conditional formatting. The relative row reference means B2 and B3 look at B2 instead of A2.

The second formula (that cuts off in the image) uses a SUBSTITUTE function to remove all the 'H's and confirm that all that remains is the lone 'L'. This means that the presence of any other letter (such as 'G' will cause this pattern to not match):

=AND(LEFT($A2,2)="LH", SUBSTITUTE($A2,"H","")="L")

For completeness I am showing the format numbers in column B. The formula in B2 is a concatenation of all the conditions:

=IF(LEFT(A2,2)="HL",1,IF(AND(LEFT(A2,2)="LH",SUBSTITUTE(A2,"H","")="L"), 2, IF(RIGHT(A2,2)="LL",3,IF(RIGHT(A2,2)="HL",4,-1))))