Macro Or Formula To Split Cells

[Dazzawm]

Please see table below. I need a formula or macro to split the cells below. Before you say use Text To Columns I have already tried that and it doesn't do it as I need!

</B>

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px"><COL style="WIDTH: 111px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="FONT-WEIGHT: bold">Before</TD><TD> </TD><TD style="FONT-WEIGHT: bold">After</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>1.8 02/04-12/06</TD><TD> </TD><TD style="TEXT-ALIGN: left">1.8</TD><TD style="TEXT-ALIGN: left">02</TD><TD style="TEXT-ALIGN: left">2004</TD><TD style="TEXT-ALIGN: left">12</TD><TD style="TEXT-ALIGN: left">2006</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>2.2D 09/97-12/10</TD><TD> </TD><TD style="TEXT-ALIGN: left">2.2D</TD><TD style="TEXT-ALIGN: left">09</TD><TD style="TEXT-ALIGN: left">1997</TD><TD style="TEXT-ALIGN: left">12</TD><TD style="TEXT-ALIGN: left">2010</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>3.2 01/00-12/00</TD><TD> </TD><TD style="TEXT-ALIGN: left">3.2</TD><TD style="TEXT-ALIGN: left">01</TD><TD style="TEXT-ALIGN: left">2000</TD><TD style="TEXT-ALIGN: left">12</TD><TD style="TEXT-ALIGN: left">2000</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD>1.6D 04/02-03/06</TD><TD> </TD><TD style="TEXT-ALIGN: left">1.6D</TD><TD style="TEXT-ALIGN: left">04</TD><TD style="TEXT-ALIGN: left">2002</TD><TD style="TEXT-ALIGN: left">03</TD><TD style="TEXT-ALIGN: left">2006</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD>2.0 06/02-</TD><TD> </TD><TD style="TEXT-ALIGN: left">2.0</TD><TD style="TEXT-ALIGN: left">06</TD><TD style="TEXT-ALIGN: left">2002</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD>2.2D 08/91-</TD><TD> </TD><TD style="TEXT-ALIGN: left">2.2D</TD><TD style="TEXT-ALIGN: left">08</TD><TD style="TEXT-ALIGN: left">1991</TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>

 

[truskawa]

Hi,

I would suggest Text to Columns. Wouldn't write a macro for this.
Don't know how representative is your abstract of data given here. But with what you gave us I would do:

1. Text to columns: delimiter "-" and make sure both columns are kept as text
2. on the remaining text that is still together (e.g. from row 2 "1.8 02/04")
I would do tez to columns delimiter " " (space) also make sure that created column is not a date
3. right now you should have first column of output (C) and two dates together. Now just do text to columns on both dates with delimiter "/". Now just make adjustments to year column to make it 4 digits and output should be the same as yours.

If this doesn't work for you write why.

 

[Dazzawm]

Its going all over the place! The years end up as Dec-97, Jan-00 etc.. Someone has given me a formula for this before but I just can't find it.

 

[truskawa]

How can I help you if you don't read carefully? I wrote that you have to make sure that during text to columns you mark all columns as text! you will avoid changing it to date.

It works!