1

I need to be able to grab the birth year in a summary of a number of people. I know how to grab exactly 4 digits and I know how escape any digit. But I cannot seem to be able to combine the two.

Here is my code for finding everything but digits [^[\d{4}]. Unfortunately it omits all digits - not exclusively the ones with 4 digits.

Has any anyone got an idea?

  • I'm unclear what you're asking. Could you edit your question to give an example of a Birth Year how it is and what you need to do with it? – Ryan Aug 5 '15 at 14:31
  • FYI: that GREP matches everything Not that is between the [..] characters. So that includes the curly quotes. You may want to brush up on them. – usr2564301 Aug 5 '15 at 17:36
  • This is my challenge. I want to replace this column of birth dates with the year only. 04/05/1983 04/01/1956 07/09/1944 01/03/1973 16/08/1976 09/12/1975 – Jakob Thuemoes Aug 12 '15 at 8:20
2

Here is how I solved it.

First I moved back to my Google spreadsheet using Regex.

I did the thing in 2 steps which could probably be joined together.

  1. I want to use the =REGEXREPLACE function that recognizes a certain string pattern. But as this function only accepts strings and not dates or numbers I add a character "A" to the dates in a new column (C) like so: =JOIN(""; B2 ;"A"). The first parameter is an empty divider that I have no use for.

  2. I then run this code onto the new column: =REGEXREPLACE(C2; "\d{2}\D\d{2}\D"; "") into a new column. Basically it finds the day+month parts by using the pattern of digit.digit.nondigit.digit.digit.nondigit (nondigit being the seperators "/" and the added charatcer "A"). All of this is then replaced with a empty string "" which leaves the remaining 4 digits.

1

There is no operator for not matching sequences in regular expressions (just items to match [^a] is translated to anything but a, [^ab] is anything but a or b not b a followed by b). It is still a matching statement

There is however a negative look ahead which says if this happens discard match. This however does not work in the general case only if its positional. To give a better answer you should post some examples of what your actually matching.

An alternate strategy might to match everything then discard the unwanted things. For example if you wanted to delete all except years in:

Benjamin 1996 Mark 1999 Mia 2005

And used find:

.*?(\d{4})[^\d]*

Replace:

Year: $1~b

would result in:

Year: 1996
Year: 1999
Year: 2005

When replace all is chosen. If you want to do the reverse then just match for \d{4} and replace with nothing...

  • For clarity, the $1 is defined by the () around the \d{4} expression. – Yorik Aug 5 '15 at 21:43
  • Good idea. I'll give it a go. – Jakob Thuemoes Aug 12 '15 at 8:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.