Formule to split data (from "1999 - 2001" to "1999 2000 2001")

**Krikke001** · 02-23-2011, 09:34 AM

Hi Everybody,

8've been looking for a few days for a formula that allows me to write in full certain years:

This is what I mean:
Value: 1999 - 2004
Value after formula: 1999 2000 2001 2002 2003 2004

Need something in the direction of
2004-1999 = 5
1999 (1999+1) (1999 +2) (1999 +3) (1999+4) (1999+5)
or 1999 (1999+1) ((1999+1)+1) etc until 5 is reached

Thanks for looking at this.

**DonkeyOte** · 02-23-2011, 09:55 AM

Assuming the spans vary then in reality a single cell native formula is not really viable / practical (IMO)

You could however consider using a basic UDF:

Please Login or Register  to view this content.

The above stored in a standard Module in VBE could be called from a cell along the lines of:

Please Login or Register  to view this content.

where A1 holds 1999-2004 etc.. this would return the delimited string.

**Krikke001** · 02-23-2011, 10:59 AM

Worked!

Just had to do a figure out the VBE,...

Butt it did the trick,

Thank you very much

**martindwilson** · 02-23-2011, 12:37 PM

=if($a1="","",if(column(a1)-1>right($a1,4)-left($a1,4),"",left($a1,4)-1+column(a1)))

**Krikke001** · 09-21-2011, 05:52 AM

Hi Everybody,

I would like to come back to this topic.
You've helped me a lot with the split years formula.

Now I need to reverse it.

From:
2001 2002 2003 2004 2005

To:
2001 - 2005

Can anyone push me in the right direction?

Thanks,

**zbor** · 09-21-2011, 05:55 AM

Here: =LEFT(C6,4)&" - "&RIGHT(C6,4)

**Krikke001** · 09-21-2011, 06:37 AM

Originally Posted by zbor

Here: =LEFT(C6,4)&" - "&RIGHT(C6,4)

Thanks, it worked!

Only had to change , to ;

=LEFT(C6;4)&" - "&RIGHT(C6;4)

**zbor** · 09-21-2011, 06:40 AM

Glad to help...

I guess I shouldn't change ; to , in a first place

**Krikke001** · 09-21-2011, 06:48 AM

and to keep it interesting;-)

Now I have the following issue:

Orginal:
2001 2002 2003 2004 2005
2003 2004 2005 2006 2007 2008 2009 2010
2003
2005 2006

After formula:
2001 - 2005
2003 - 2010
2003 - 2003
2005 - 2006

When there's only 1 year available it should stay one year!
See the 2003 value.Any work around for this?

Thanks,

**zbor** · 09-21-2011, 06:53 AM

There can be several approaches... You could check LEN, or ISNUMBER etc.

This approach look is there a space in the word (note: if you have 2003_ (space) you will get 2003-2003 again)...

=LEFT(G12;4)&IF(ISERR(FIND(" ";G12));"";" - "&RIGHT(G12;4))