I have a google sheet with only one column:
| client_id |
|---|
| 1234 - Act |
| 4523 - Tdf |
| 1111 - XXX |
| 1234 - Act12 |
Right now I have conditional formatting that turns the cells red color if a client_id with the same value already exists. My function for that is:
`=SUMPRODUCT(TRIM($A$8:$A$9999965)=TRIM(A8))>1`
However, my client requests a little change, right now he doesn't want to compare if the entire client_id value already exists but in the case of the Number part. So with my previous formula, the client 1234 will not appear as Red because the String is different and he needs to know that a client with the number 1234 already exists on the database.
I try with this formula:
=SUMPRODUCT(TRIM(LEFT($A$8:$A$9999971, SEARCH("-",TRIM($B$8))))=TRIM(LEFT($B$8, SEARCH("-",TRIM($B$8)))))>1
But it does not detect the red even when the same value is registered and I got the following error:
`Function SEARCH parameter 2 value should be non-empty.`
Does anyone know how I can get this?
Many thanks for your help
You could try with SPLIT or REGEXEXTRACT.
=SUMPRODUCT(INDEX(TRIM(SPLIT(A$8:A,"-")),,1)=INDEX(TRIM(SPLIT(A8,"-")),,1))>1
=SUMPRODUCT(REGEXEXTRACT(A$8:A,"\d+")=REGEXEXTRACT(A8,"\d+"))>1