Regex Expression with Conditional formatting in a Google Sheet with tracts
So I have to do some conditional formatting for a google spread sheet. Essentially I need to ensure that of a set of data, all of the census tracts that exist in the county need. I want to use conditional formatting to automatically highlight all of the tracts that is in the data set that don't exist in the county. I have a long chain of regex that is not working right now but it contains the data set of tracts that exist in the county. Any suggestions for better solutions would be great.
=NOT(REGEXMATCH(G1,"^(?:5001|5002|5003|5004|5005|5006|5008|5009.01|5009.02|5010|5011.01|5011.02|5012|5013|5014.01|5014.02|5015.01|5015.02|5016|5017|5018|5019|5020.01|5020.02|5021.01|5021.02|5022.01|5022.02|5023.01|5023.02|5024|5025|5026.01|5026.03|5026.04|5027.01|5027.02|5028|5029.01|5029.02|5029.03|5029.06|5029.07|5029.08|5029.09|5029.10|5030.01|5030.02|5030.03|5031.05|5031.08|5031.10|5031.11|5031.12|5031.13|5031.15|5031.16|5031.17|5031.18|5031.21|5031.22|5031.23|5032.04|5032.07|5032.08|5032.10|5032.11|5032.12|5032.13|5032.14|5032.17|5032.18|5033.04|5033.05|5033.06|5033.12|5033.13|5033.15|5033.21|5033.22|5033.23|5033.24|5033.25|5033.26|5033.27|5033.29|5033.30|5033.31|5033.32|5033.33|5033.34|5033.36|5033.37|5034.01|5034.02|5035.04|5035.06|5035.07|5035.08|5035.09|5035.10|5035.11|5036.01|5036.02|5037.03|5037.07|5037.08|5037.09|5037.10|5037.11|5037.12|5037.13|5038.02|5038.03|5038.04|5039.02|5039.03|5040.01|5040.02|5041.01|5041.02|5042.01|5042.02|5043.07|5043.08|5043.11|5043.14|5043.15|5043.16|5043.17|5043.18|5043.19|5043.20|5043.21|5043.22|5043.23|5044.10|5044.11|5044.12|5044.13|5044.14|5044.15|5044.16|5044.17|5044.18|5044.20|5044.21|5044.22|5044.23|5045.04|5045.05|5045.06|5045.07|5046.01|5046.02|5047|5048.02|5048.03|5048.05|5048.06|5049.01|5050.01|5050.06|5050.07|5050.08|5050.09|5051|5052.02|5052.03|5053.01|5053.02|5053.03|5053.04|5053.05|5054.01|5054.02|5054.03|5055|5056|5057|5058|5059|5060|5061.01|5061.02|5061.03|5062.02|5062.03|5062.04|5063.01|5063.02|5063.04|5063.05|5064.01|5064.02|5065.01|5065.02|5065.03|5066.01|5066.03|5066.04|5066.05|5066.06|5067.01|5067.02|5067.03|5068.01|5068.02|5068.03|5068.04|5069|5070.01|5070.02|5071|5072.03|5072.05|5072.06|5073.01|5073.02|5074.01|5074.02|5075|5076|5077.01|5077.02|5077.03|5078.05|5078.06|5078.07|5078.08|5079.03|5079.04|5079.05|5079.06|5080.01|5080.03|5080.04|5081.01|5081.02|5082.02|5082.03|5082.04|5083.01|5083.03|5083.04|5084.01|5084.03|5084.04|5085.03|5085.04|5085.05|5085.07|5085.08|5086.01|5086.02|5087.03|5087.04|5088|5089|5090|5091.02|5091.05|5091.06|5091.07|5091.08|5091.09|5092.01|5092.02|5093.02|5093.03|5093.04|5094.01|5094.03|5094.04|5095|5096|5097|5098.01|5098.02|5099.01|5099.02|5100.01|5100.02|5101|5102|5103|5104|5105|5106|5107|5108.01|5108.02|5108.03|5109|5110|5111|5112|5113.01|5113.02|5114|5115|5116.08|5116.09|5117.01|5117.02|5117.04|5117.05|5117.07|5118|5119.05|5119.07|5119.09|5119.10|5119.11|5119.12|5119.13|5119.14|5119.15|5119.16|5120.01|5120.05|5120.17|5120.19|5120.20|5120.21|5120.22|5120.23|5120.24|5120.25|5120.26|5120.27|5120.29|5120.30|5120.31|5120.32|5120.33|5120.34|5120.35|5120.36|5120.37|5120.38|5120.39|5120.42|5120.43|5120.45|5120.47|5120.52|5120.53|5121|5122|5123.05|5123.07|5123.08|5123.09|5123.10|5123.11|5123.12|5123.13|5123.14|5124.01|5124.02|5125.03|5125.05|5125.06|5125.08|5125.09|5125.10|5126.02|5126.03|5126.04|5130|5135)$"))
This does not seem like a good use of regex. You should embed that list somewhere in the spreadsheet and then in your conditional formula use a vlookup inside an isna to deliver a true/false whether it is in the list.
Unfortunately, Google Sheets does not allow the conditional formula to reference off-sheet ranges, so you'll have to keep both lists on the same sheet.
- Python RegEx: match words in a string only if they are not preceded by a specific character
- Regular expression that ignores HTML entities
- Allow access to page only from certain referrer
- Regex to match string containing two names in any order
- Matching boot times from log using sed -E
- Validate Bangladeshi phone number with optional +88 or 01 preceeding 11 digits
- Split address and numbers
- Using setCustomValidity stops input from validating
- Use dynamic (variable) string as regex pattern in dart
- Regular expression matching Android package name
- Replace commas not inside single quotes with an @ symbol
- Visual Studio Code Search and Replace with Regular Expressions
- Split string on commas not inside double quotes
- Replace inner letters inside words that start with and end with a specific letter with the same amount of a specific character
- regex for expression with one separator hyphen
- Extracting specific data from a string with regex and Powershell
- Parse datetime string in Ydm.His format
- Extract all words double wrapped in curly braces
- Parse dot-delimited video filename to extract show title, series number and episode number
- Split a string into words, preserving contractions but removing leading apostrophes from words
- Split string on pipes which are not between square braces
- Simple regex pattern for email
- How to extract filename without extension in Makefile
- Regex pattern containing backlashes causes failure in preg_replace()
- preg_replace() pattern starting with < and ending with > doesn't find HTML comments as intended
- Split a string on spaces and/or commas after a word which may be preceded by a small word
- How to convert split() to preg_split()?
- Parse a string without delimiters into a 2d array
- Regex to extract WordPress wp-config database information and automating it with ansible to update a shared hosting environment
- Input regex for UK postcodes constantly invalid