pulling multiple date ranges from 1 cell into multiple rows?

**13lack13lade** · 06-28-2013, 12:57 AM

Hi all!

just wondering if its possible at all to list multiple date ranges from 1 cell into multiple rows in the same column? i think want to run a lookup for the date ranges vs the season rates to automatically complete into a template i have for data uploading. example as per below.

Season 1 21Jan13-31Feb13; 01mar13-02mar14; 03mar14-24mar14
Season 2 again multiple date ranges separated by ;
Season 3 again multiple date ranges separated by ;
Season 4 again multiple date ranges separated by ;

This is how I want the season 1 ranges to paste into excel - separate cells

21Jan13 31Feb13
01Mar13 02Mar14
03Mar14 24Mar14

Tried to ScreenShot - didnt want to upload though

Appreciate any help guys! thanks!

**humdingaling** · 06-28-2013, 04:26 AM

very quick way of doing it
text to column>>delimiter>>use ;

after its split you can do whatever you want

**XOR LX** · 06-28-2013, 04:33 AM

Hi,

It would perhaps make more sense, since your data is in columns, to list each of the separate date ranges in separate columns to the right of each, rather than in rows below as you indicate. If you want it that way, you will just have to ensure that you have enough rows free as equals the number of separate strings in each.

Assuming your first entry is in cell A1, enter this in e.g. B1 and copy down for as many rows as there are separate clauses in A1:

=IFERROR(MID($A$1,FIND("@",SUBSTITUTE(";"&$A$1,";","@",ROWS($A$1:$A1))),FIND("@",SUBSTITUTE($A$1&";",";","@",ROWS($A$1:$A1)))-FIND("@",SUBSTITUTE(";"&$A$1,";","@",ROWS($A$1:$A1)))),"")

Regards