Click here to Skip to main content
15,884,177 members
Home / Discussions / Regular Expressions
   

Regular Expressions

 
GeneralRe: How to insert a space at the beginning of a line in a "for next" code loop (.NET regular expressions) Pin
Andre Oosthuizen5-Jul-23 1:15
mveAndre Oosthuizen5-Jul-23 1:15 
GeneralRe: How to insert a space at the beginning of a line in a "for next" code loop (.NET regular expressions) Pin
Richard MacCutchan5-Jul-23 2:21
mveRichard MacCutchan5-Jul-23 2:21 
GeneralRe: How to insert a space at the beginning of a line in a "for next" code loop (.NET regular expressions) Pin
jschell5-Jul-23 5:31
jschell5-Jul-23 5:31 
QuestionNeed help with Regular expression Pin
Member 1601263423-May-23 9:36
Member 1601263423-May-23 9:36 
AnswerRe: Need help with Regular expression Pin
PIEBALDconsult23-May-23 9:39
mvePIEBALDconsult23-May-23 9:39 
AnswerRe: Need help with Regular expression Pin
jschell24-May-23 5:58
jschell24-May-23 5:58 
QuestionRedirection with exclusions RegEx Pin
Member 1598311319-Apr-23 3:07
Member 1598311319-Apr-23 3:07 
QuestionI need help with a complex regex Pin
Member 127465326-Apr-23 3:57
Member 127465326-Apr-23 3:57 
Challenge:
I have a file with genealogy information which I would like to extract (in Google Sheets) using regex.

Data:
One cell contains text information. Basically it is four main parts, two of which are optional and can have slightly different formats and contents

First comes always a number followed by a period. (This is the generation number.)
Second comes the name. It consists of one or more first and last names
These two are always there

They can be followed by birth and/or death information
If there is birth information, it always comes directly after the name and starts with "b. ".
It can have a date, and or a location
The date can be preceded by "circa", "before" and "before circa". It is then followed by either a 4 digit year, or more commonly by the month name, date, and year. Example: "March 4, 1888"
After the year might follow a location (free text)

If there is death information, it starts with "d. " and can contain the same information as above, i.e. a date and/or a location.

My best shot is close, but not handling the special cases of "before" etc too well:
=ARRAYFORMULA(IFERROR(SPLIT(REGEXREPLACE(A:A,"^(\d+)\.\s(.+?)(\s(b\.?\s?(\w+\s\d{1,2},\s\d{4})?,?\s?(.*?))?(; d\.\s(\w+\s\d{1,2},\s\d{4})?, \s?(.+)?)?)?$","$1|$2|$3|$4|$5|$6|$7|$8|$9"),"|")))


So the regex part of it is:
^(\d+)\.\s(.+?)(\s(b\.?\s?(\w+\s\d{1,2},\s\d{4})?,?\s?(.*?))?(; d\.\s(\w+\s\d{1,2},\s\d{4})?, \s?(.+)?)?)?$


It works well for entries like this one:
2. Gunnar Helg Andersson b. October 22, 1921, Ormöga No. 3, Bredsättra, Kalmar, Sweden; d. January 1, 2021, Köpingsvik


But not for entries like:
7. Kierstin Danielsdotter b. before circa 1706
9. Lussa Elofsdotter b. circa 1680; d. May 16, 1758, Bredsättra
7. Olof Jönsson b. 1742, Sverige (Sweden); d. September 4, 1811
9. Nils Knutsson b. circa 1676, Istad, Alböke; d. circa April 17, 1729

AnswerRe: I need help with a complex regex Pin
Terry R 202312-Apr-23 10:53
Terry R 202312-Apr-23 10:53 
AnswerRe: I need help with a complex regex Pin
Richard MacCutchan12-Apr-23 20:41
mveRichard MacCutchan12-Apr-23 20:41 
GeneralRe: I need help with a complex regex Pin
jschell13-Apr-23 5:29
jschell13-Apr-23 5:29 
GeneralRe: I need help with a complex regex Pin
Richard MacCutchan13-Apr-23 5:52
mveRichard MacCutchan13-Apr-23 5:52 
GeneralRe: I need help with a complex regex Pin
jschell14-Apr-23 11:28
jschell14-Apr-23 11:28 
GeneralRe: I need help with a complex regex Pin
Member 1274653217-Apr-23 20:50
Member 1274653217-Apr-23 20:50 
GeneralRe: I need help with a complex regex Pin
Richard MacCutchan17-Apr-23 22:04
mveRichard MacCutchan17-Apr-23 22:04 
QuestionWhat is the regular expression for this string? Pin
Member 1595912122-Mar-23 11:29
Member 1595912122-Mar-23 11:29 
AnswerRe: What is the regular expression for this string? Pin
Richard MacCutchan22-Mar-23 23:12
mveRichard MacCutchan22-Mar-23 23:12 
GeneralRe: What is the regular expression for this string? Pin
k505423-Mar-23 3:52
mvek505423-Mar-23 3:52 
GeneralRe: What is the regular expression for this string? Pin
Richard MacCutchan23-Mar-23 3:55
mveRichard MacCutchan23-Mar-23 3:55 
AnswerRe: What is the regular expression for this string? Pin
jschell23-Mar-23 6:24
jschell23-Mar-23 6:24 
AnswerRe: Where to start - need to extract...using regular expression Pin
k505415-Mar-23 10:39
mvek505415-Mar-23 10:39 
QuestionRegular expression to return all keys in a Bibtex file Pin
Member 159423565-Mar-23 5:46
Member 159423565-Mar-23 5:46 
AnswerRe: Regular expression to return all keys in a Bibtex file Pin
Richard Deeming5-Mar-23 21:36
mveRichard Deeming5-Mar-23 21:36 
GeneralRe: Regular expression to return all keys in a Bibtex file Pin
Member 159423566-Mar-23 19:48
Member 159423566-Mar-23 19:48 
AnswerRe: Regular expression to return all keys in a Bibtex file Pin
jschell8-Mar-23 6:57
jschell8-Mar-23 6:57 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.