Ask Your Question

Extract value from cell based on regex [closed]

asked 2019-03-30 11:23:39 +0200

hobbit32 gravatar image

Good morning everyone,

I am struggling for hours regarding what I think is a simple operation ... but it isn't. Could you please help me with this issue ?

I have a column full of text including dates based on this format (DD/MM)

image description

I would like to extract the date in the next column to get this result :

image description

The regex is ok but my main questions are :

  1. how to search for the regex in the first column
  2. and how to copy it (only the date) to the next column

Thank you for your help !


edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by hobbit32
close date 2019-03-31 16:56:39.512547


The regex is ok

Which? how could others advise you what to do with what parts of your regex, if you don't give what you have?

Mike Kaganski gravatar imageMike Kaganski ( 2019-03-30 11:42:19 +0200 )edit

Good afternoon,

Thank you so much for your quick answers.

Both suggestions work well ! I also got a good reason to upgrade to 6.2 :)

Regarding the regex I use : [0-9][0-9][/][0-9][0-9]

Have a good day ! Hobbit

hobbit32 gravatar imagehobbit32 ( 2019-03-31 16:55:06 +0200 )edit

2 Answers

Sort by » oldest newest most voted

answered 2019-03-30 13:36:21 +0200

gregors15 gravatar image

Try this, =MID(A2,SEARCH("/",A2,1)-2,5) However it assumes that the "/" only appears once in a cell.

edit flag offensive delete link more

answered 2019-03-30 13:53:51 +0200

If you use 6.2 (or later ;-)), you may use a column with new REGEX spreadsheet function.

Or you can simply copy your column to the next one (pasting only data, not formulas), and do find-and-replace on that column, using ^.*\b(\d{1,2}/\d{1,2})\b.*$ as search string, and $1 as replacement. Don't forget to select the column, and check Current selection only and Regular expressions in Find & Replace dialog.

edit flag offensive delete link more

Question Tools

1 follower


Asked: 2019-03-30 11:23:39 +0200

Seen: 681 times

Last updated: Mar 30 '19