Extract text from a cell without extracting punctuation in Google Sheets
In Column F, I have a list of states and their abbreviations.
I want to use a regex function to extract the state and the state code so the output looks like the contents in Columns G and H.
How do I do this? Thanks!
When I type in:
=regexextract(F3,"(\w+)(\s\w+)?")
The function works for states with one word names like Alaska and Florida, but separates the contents of multi-word states into multiple columns. it also fails to capture all three words in "District of Columbia". I could fix it to capture 3 words, but it wouldn't resolve the fundamental problem of keeping the output in a single cell in a single column.
I am at a loss of how to extract the state code from the quotation marks.
You can capture word characters optionally repeated by a space and again word characters in the first group, and capture all between the double quotes in the second group.
=regexextract(F2,"(\w+(?:\s+\w+)*),""([^""]*)""")
- Return elements from a list that match a pattern
- convert/compile regular expressions to C code
- How to make the regex negative lookbehind consume discarded text?
- Generate words that fit in Guids (just for fun)
- Insert newlines before sequences of all-caps words
- Convert all uppercase words in a string to title case
- Add a character in between regex
- RegExp testing fails when string is validated in RegExp tools
- VSCODE snippet PHP to fill namespace automatically
- Regular expression to extract text between square brackets & last string out of the square bracket
- What is the difference between a plus and a star in regex?
- Extract class names from JAR with special formatting
- How to replace a comma with a dot in a number (or any replacement)
- regex to match at certain string and not followed by another string using notepad++
- RegEx for matching UK Postcodes
- Why does the grep command select more than expected?
- Regex for ISO 8601 durations
- Regular expression for number with length of 4, 5 or 6
- Regex to match a whole number not ending in some digits
- Can anyone lead me to DEFINE assertions documentation for regular expressions?
- Regex to find string not followed by separator and 1 or 2 digits
- Regex for quoting unquoted XML attributes
- Regex to extract all the rows from CSV - Apache Nifi
- 12-digit regex for phone number
- regex to exclude lowercase only strings, but save strings which contain capital
- Extract year with an optional plus right after from a string using PHP regex
- missing quotation marks on keys in JSON
- Regex for a valid hashtag
- Enumerate Possible Matches of Regular Expression in Java
- camelCase to underscore in vi(m)