Retrieving start and end dates from a single cell

[McKneezy]

Hello,

I am attempting to extract all dates from a single cell. Each single cell may contain at least 1 start and end date. In all cases the start and end dates are separated by a line break.

My data set contains many rows where sometimes I would require to extract all these dates and separate each start and end date in their own column.

I've enclosed a sample workbook for clarification purposes.

If anyone can assist me that would be great.

Thank you!

Mckneezy

[mehmetcik]

Paste this formula in D3 and fill right until you get errors

=TRIM(MID($B3,FIND("|",SUBSTITUTE(SUBSTITUTE($B3,CHAR(10)," "),"/","|",COLUMN()*2-7))-2,10))


Or

Paste this in D3 and fill right until you get blanks

=IFERROR(TRIM(MID($B3,FIND("|",SUBSTITUTE(SUBSTITUTE($B3,CHAR(10)," "),"/","|",COLUMN()*2-7))-2,10)),"")

[hrlngrv]

For column D, D3: =TRIM(CLEAN(MID(B3,SEARCH("??/?",B3),10))) . For subsequent columns, E3: =TRIM(CLEAN(MID($B3,SEARCH("??/?",$B3,FIND(D3,$B3)+10),10))) and fill right.

This assumes all dates would be m/d/yyyy, m/dd/yyyy, mm/d/yyyy or mm/dd/yyyy, so 8 to 10 characters. Using MID() with 10 as 3rd argument means pulling up to 2 characters besides the date, but those characters would be either newlines or spaces. CLEAN() eliminates newlines, and TRIM() eliminates leading and trailing spaces.

[McKneezy]

Thanks for the quick response mehmetcik. Unfortunately this solution does not work in my data set, as I think it has to do with referencing differences between the sample workbook and my data set correct?

The following response I was provided by hrlngrv worked great, so no need for us to workout another solution.

However, can you explain the concept of the formula provided for learning purposes? I know the IFERROR, TRIM, MID, FIND, SUBSTITUTE, and CHAR part of the formula. It is the ',COLUMN()*2-7))-2' part which gets me. I also do not understand the "|" symbol in terms of what it represents.

Thanks again mehmetcik and Happy New Year!

[McKneezy]

Thanks hrlngrv. Works like a charm!
I will mark as solved when I hear back from mehmetcik.

[mehmetcik]

=COLUMN()*2-7 returns 2 of column D and 4 for column E and increases by two as the column number increases.

the Substitute function replaces th "/" in B3 specified by =COLUMN()*2-7 to a "|" which is a character rarely used.

so if we find "|" in the modified version of B3 in each column we will be in the middle of a date "1|10/2018"

So if we take ten characters starting two characters before the "|" we will end up with " 1|10/2018"

But we take the ten characters from the original unchanged B3 " 1/10/2018"

The trim gets rid of the leading space, just in case we have a single digit date. 1/10/2018 as opposed to 10/10/2018

[McKneezy]

Appreciate it mehmetcik, very good explanation might I add.

Happy New Year!

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.