Excel - Extract All Dates from Text

Question

Let's say I have the following cases:

Today's Date is 08/20/2020 and tomorrow's date is 30/10/2018. Status = N/A
Yesterday's Date status is stuff/things but it  is 2020/08/31 and today is 8/20/18.

What I want to return is

08/20/2020, 30/10/2018
2020/08/31, 08/20/18

Any suggestions on how to go about doing this?

Use find() and look for the "/", then left() mid() and right() as necessary — Solar Mike, Aug 24 '18 at 15:12
That doesn't work because there are other values that use the "/" etc, for example "N/A" — Chicken Sandwich No Pickles, Aug 24 '18 at 15:13
VBA seems like the natural solution. Unfortunately, the Excel worksheet formula language is weak for text processing. See [this question](https://stackoverflow.com/q/22542834/4996248) for how to do this in Excel. — John Coleman, Aug 24 '18 at 15:14
Is each line a single cell? How's the data presenting in Excel? — BruceWayne, Aug 24 '18 at 15:15
Correct, each line is in a single cell, so "A1" = first row and "B1" = second row — Chicken Sandwich No Pickles, Aug 24 '18 at 15:17
All values are contained into those cells, there is no spill over into "A2", "A3", etc — Chicken Sandwich No Pickles, Aug 24 '18 at 15:17

Scott Craner · Accepted Answer · 2018-08-24T15:26:59.180

1

Use TEXTJOIN in an array formula:

=SUBSTITUTE(TEXTJOIN(", ",TRUE,IF(ISNUMBER(SEARCH("*/*/*",TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),(ROW($1:$25)-1)*99+1,99)))),TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),(ROW($1:$25)-1)*99+1,99)),"")),".","")

Being an array formula it needs to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.

TEXTJOIN was introduced with Office 365 Excel

If you do not have Office 365 then the output will need to be in different cells for each "date"

Put this in B1, copy over and down:

=IFERROR(SUBSTITUTE(TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",99)),(AGGREGATE(15,6,ROW($1:$25)/(ISNUMBER(SEARCH("*/*/*",TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",99)),(ROW($1:$25)-1)*99+1,99))))),COLUMN(A:A))-1)*99+1,99)),".",""),"")

edited Aug 24 '18 at 15:26

answered Aug 24 '18 at 15:18

Scott Craner

148,073
10
49
81

I didn't have any luck with this, if it helps, I'm using 100 records. I am also using the correct version of excel – Chicken Sandwich No Pickles Aug 24 '18 at 15:26
1

@LunchBox I do not know what you mean by: `I didn't have any luck with this`. Please be more specific. As you can see it works for me, so I have not idea where to start helping you resolve the issue. – Scott Craner Aug 24 '18 at 15:29
@LunchBox Are you sure you used CNTRL+SHFT+ENTER instead of just ENTER when you finished the formula? You will know you did it right when you see { } around the formula in the formula bar. These { } cannot be added manually. – Forward Ed Aug 25 '18 at 02:48
Originally, due to a time crunch, I was going to try to manipulate this in Excel but I couldn't get this to work for me. I am aware of how to add the brackets though. I just said screw it and did it in python. I am going to mark this as the answer so you get the points, but I just couldn't get it to work and solved this in python. – Chicken Sandwich No Pickles Aug 25 '18 at 04:31

Excel - Extract All Dates from Text

1 Answers1