Split cell into two cells

[tonez90]

I tried looking in the forum and could not find anything that may assist me.

I have a cell (C) which may contain:
<TABLE style="WIDTH: 329pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=439 border=0 x:str><COLGROUP><COL style="WIDTH: 329pt; mso-width-source: userset; mso-width-alt: 16054" width=439><TBODY><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 329pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=439 height=20>Black Powder, (Gunpowder) granular (UN No. 0027)</TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 329pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=439 border=0 x:str><COLGROUP><COL style="WIDTH: 329pt; mso-width-source: userset; mso-width-alt: 16054" width=439><TBODY><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 329pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=439 height=20>White Powder, granular (UN No. 0027)</TD></TR></TBODY></TABLE>
What I am trying to do if split the C Column into two
C AND D

Cells C would contain
<TABLE style="WIDTH: 329pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=439 border=0 x:str><TBODY><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 329pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=439 height=20>Black Powder, (Gunpowder) granular</TD></TR></TBODY></TABLE><TABLE style="WIDTH: 329pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=439 border=0 x:str><COLGROUP><COL style="WIDTH: 329pt; mso-width-source: userset; mso-width-alt: 16054" width=439><TBODY><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 329pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=439 height=20>White Powder, granular</TD></TR></TBODY></TABLE>

Cells D would contain
<TABLE style="WIDTH: 329pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=439 border=0 x:str><TBODY><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 329pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=439 height=20>(UN No. 0027)</TD></TR></TBODY></TABLE><TABLE style="WIDTH: 329pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=439 border=0 x:str><COLGROUP><COL style="WIDTH: 329pt; mso-width-source: userset; mso-width-alt: 16054" width=439><TBODY><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 329pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=439 height=20>(UN No. 0027)</TD></TR></TBODY></TABLE>

Can anyone advise me of how to do this?

Thanks in advance

 

[Blade Hunter]

Is it always UN at the end?

Is the last part always going to be in brackets?

 

[Blade Hunter]

Maybe this??

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:312px;" /><col style="width:241px;" /><col style="width:98px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-family:Arial Unicode MS; font-size:10pt; ">Black Powder, (Gunpowder) granular (UN No. 0027)</td><td >Black Powder, (Gunpowder) granular</td><td >(UN No. 0027)</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-family:Arial Unicode MS; font-size:10pt; ">White Powder, granular (UN No. 0027)</td><td >White Powder, granular</td><td >(UN No. 0027)</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B1</td><td >=TRIM(SUBSTITUTE<span style=' color:008000; '>(A1,C1,"")</span>)</td></tr><tr><td >C1</td><td >="(" & TRIM(RIGHT<span style=' color:008000; '>(SUBSTITUTE<span style=' color:#0000ff; '>(A1,"(",REPT<span style=' color:#ff0000; '>(" ",100)</span>)</span>,100)</span>)</td></tr><tr><td >B2</td><td >=TRIM(SUBSTITUTE<span style=' color:008000; '>(A2,C2,"")</span>)</td></tr><tr><td >C2</td><td >="(" & TRIM(RIGHT<span style=' color:008000; '>(SUBSTITUTE<span style=' color:#0000ff; '>(A2,"(",REPT<span style=' color:#ff0000; '>(" ",100)</span>)</span>,100)</span>)</td></tr></table></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4


Dan

 

[tonez90]

Hi And thanks for quick response. In answer to your questions. No the last bit is not always in brackets and there may not be a bracketted part at the end. I Know this may cause an error but this will be the exception rather than the rule. and if there is a UN number it will always be at the end.

What you have in the table is what I am after but I am unclear of how you got yhe out put with the formulae in your speadsheet formulae section? but this si exactly what I am after.

 

[Blade Hunter]

Basically I take each ( and replace it with 100 spaces.

I then take the right 100 chars which I am guaranteed will be what you want plus a lod of spaces.

Trim it to remove the spaces and add a ( back to the start

That is in column C

Column B then relies on Column C by replacing the contents of column C in the contents of column A with nothing then trims to remove any trailing spaces.

 

[Sandeep Warrier]

Also,

Excel Workbook
	A	B	C
1	Black Powder, (Gunpowder) granular (UN No. 0027)	Black Powder, (Gunpowder) granular	(UN No. 0027)
2	White Powder, granular (UN No. 0027)	White Powder, granular	(UN No. 0027)
Sheet1

Excel 2003

Cell Formulas
Range	Formula
B1	=TRIM(SUBSTITUTE(A1,C1,""))
C1	=MID(A1,FIND("(UN",A1),LEN(A1))

<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden"><input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">

 

[tonez90]

Thanks, that is exactly waht I needed and it works quite well. I Learnt something new and it feels good.
Tony