Macro Or Formula To Split Cells

Dazzawm

Well-known Member
Joined
Jan 24, 2011
Messages
3,403
Office Version
  1. 2016
Platform
  1. Windows
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>
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

truskawa

New Member
Joined
Jan 27, 2011
Messages
27
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

Well-known Member
Joined
Jan 24, 2011
Messages
3,403
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
Jan 27, 2011
Messages
27
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,108,502
Messages
5,523,295
Members
409,508
Latest member
Afc

This Week's Hot Topics

Top