How do I remove a word that preceeds a character as well as the character itself
I am using IMPORTXML to grab some data and then regex to pull some text out. The part i want to extract is a name.
It always follows "Full Name:" (minus the quotes). After the name, there is always a word followed by another colon. This word could be Pronounciation, Linguistics, Type, or anything for that matter.
The closest I can get is this formula:
=IFERROR(REGEXEXTRACT(REGEXREPLACE(JOIN(" ", IMPORTXML(B13, "//div[@id='meta']")), "\s+", " "), "Full Name:\s*([A-Za-z]+(?:[-'\s][A-Za-z]+)*)"), "")
Unfortunately, that still leaves the last word in.
So if you have for example:
Full Name: Abraham Lincoln Nickname: Honest Abe.
I just want it to pull out Abraham Lincoln. That formula as I have it outputs:
Abraham Lincoln Nickname
Other examples:
- Full Name: John Smith-Rogers Finishing:
- Full Name: David Ben-Williams Prelude:
- Full Name: Sean O'Brian Nickname:
- Full Name: D'Andre Ayton Time:
- Full Name: Andy Van Slyke Name:
Respective outputs:
- John Smith-Rogers
- David Ben-Williams
- Sean O'Brian
- D'Andre Ayton
- Andy Van Slyke
For a live example: https://www.baseball-reference.com/players/l/lopezal01.shtml
The formula should output: Alfonso Ramon Lopez
https://www.baseball-reference.com/players/r/ruthba01.shtml
The formula should output: George Herman Ruth
What else do i need to do keeping in mind that i want to make sure names like O'Brien and Smith-Rogers aren't impacted?
This approach uses a slight variation of given xpath-query, which then extracts the Full Name
=let(Σ,tocol(importxml(A2,"//*[@id='meta']/*")),
+sort(regexextract(Σ,"Full Name: (.+)")))
- Regular expressions inside SQL Server
- python regex to match multiple words in a line without going to the next line
- Using String's ReplaceAll with regex
- Java - Need some help on a Regex pattern for matching both http and https
- Get Number/Number/Number structure in the given string
- Using regex in Postgres for special characters
- How can I make multiple replacements in a string using a dictionary?
- Regex to *not* match anything
- How to determine where a regex failed to match using Java APIs
- Extract user data using regex from react mention
- How to modify this regex to add up all digits to a number?
- Python Regex match or potential match
- Regex to match copyright statements
- Complex regex pattern for upper plus lower case letters and numbers
- How do I include negative decimal numbers in this regular expression?
- Regex with lookbehind searching for substring
- When transforming a vscode snippet variable, how to substitute a group with nothing?
- Select columns based on string match - dplyr::select
- Java regular expression and escaping meta characters
- Regex Powershell: find a string, then replace nth occurence of another string
- Using Java regex want to match only & but not & < etc
- Replacing multiple blank lines with one blank line using RegEx search and replace
- Using Perl Regex, how can I capture the position of the hyphens in a hyphenated word?
- How to remove square brackets and backticks from a string in Scala using regex replaceAll
- Check if String has only Integers separated by comma in PHP
- Notepad++ replace text with RegEx search result
- Regex match number between 1 and 31 with or without leading 0
- What is the right regex for my String.split()
- Limit repetitions of character to multiple fixed lengths (and not ranges)
- Cannot use boost::regex