=left, =mid, etc

BlueDingo

New Member
Joined
Oct 19, 2009
Messages
42
<TABLE style="WIDTH: 190pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=253 border=0 x:str><COLGROUP><COL style="WIDTH: 190pt; mso-width-source: userset; mso-width-alt: 9252" width=253><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 190pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=253 height=17>I have been trying to break out the following data from column A into 4 separate columns, however the only way I can do this is a laborious way of repeating the entire column A contents each time and then by hiding what I don't want.



<TABLE style="WIDTH: 190pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=253 border=0 x:str><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 190pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=253 height=17>20110401.FB14.906328150.( Blank )


</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>20110405.KL14.910765343.XJ13955/0001


</TD></TR></TBODY></TABLE>


The separation into 4 individual columns needs to look like the following:

20110401 FB14 906328150 ( Blank )

20110405 KL14 910765343 XJ13955/0001

Appreciate your assistance,
Pete



</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD></TR></TBODY></TABLE>
 
Last edited:

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Haseeb Avarakkan

Well-known Member
Joined
Sep 28, 2010
Messages
902
Office Version
  1. 365
Platform
  1. Windows
Hello,

Try Text to Columns

Select the range, go to Data | Text to Columns. make sure delimitted is selected. Next

Select Other & type "." (without quotes). Finish

A formula option,

B1, copy across.

=TRIM(MID(SUBSTITUTE("."&$A1,".",REPT(" ",100)),COLUMNS($B1:B1)*100,100))
 
Last edited:

crook_101

Well-known Member
Joined
Oct 20, 2008
Messages
682
Hi,

Is this something close:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">3</td><td style="border-right: 1px solid black;border-left: 1px solid black;background-color: #EFEFEF;;">20110405.KL14.910765343.XJ13955/0001</td><td style="text-align: right;border-left: 1px solid black;;">20110405</td><td style=";">KL14.910765343</td><td style=";">XJ13955/0001</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B3</th><td style="text-align:left">=LEFT(<font color="Blue">A3,8</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C3</th><td style="text-align:left">=MID(<font color="Blue">A3,10,14</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D3</th><td style="text-align:left">=RIGHT(<font color="Blue">A3,FIND(<font color="Red">".",A3</font>)+3</font>)</td></tr></tbody></table></td></tr></table><br />

Cheers,
Ian
 

Forum statistics

Threads
1,136,328
Messages
5,675,132
Members
419,551
Latest member
thangxpm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top