Find the earliest date within comma-separated values

**Niclal** · 08-14-2014, 06:40 AM

Good day everybody,
I need a formula or a macro to find the earliest date in comma-separated values in the same cell:

Before
A1: 20100506, 20081126, 20100525
A2: 20090408, 20100915, 20100920, 20101006, 20090323, 20110819, 20130930

After
A1: 20081126
A2: 20090323

Thank you so much

Nick

**martindwilson** · 08-14-2014, 07:06 AM

if they are like you have shown with , space seperating them try this array entered formula
=MIN(--MID(A1,ROW(INDIRECT("1:"&LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1))*10-9,8))

**martindwilson** · 08-14-2014, 10:08 AM

you pm'd me to ask "why did you multiply the values with 10, then subtracted it by 9.
ok
ROW(INDIRECT("1:"&LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1))*10-9
LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1 will give the number of strings in the list
so with 3 dates
it resolves to
ROW(INDIRECT("1:"&3))*10-9
which becomes
row(1:3)*10-9
row(1:3) is the same as this array {1;2;3}
your text stings start in positions 1,11,21 and are 8 characters long
mid(a1,{1;2;3},8) would be wrong
so
multiplying the {1;2;3} by 10 gives
{10;20;30}
this is getting there but is 9 to great for each term so subtract 9
{10;20;30}-9
becomes {1;11;21}
so now you have
mid(a1,{1;11;21},8)