How to cut large string of digits into 2 cells with 0 at the begining

[ssanguszko]

Hi,

I have large number like 20110202083011000 and I want to cut this one with VBA into 2 columns - one with 20110202 and second with 083011, rest of "000" will be deleted. I tried Mid function with many setting of format type "general", and "@" but still get errors like when cell format is set to text all zero at the end of 083000 (which is time), are converted to letters. Another one is a problem with the fact that when cell's format is set to numeric then I cant add 0 at the beginning. So I have either no 0 at the begining or have letter AE etc at the end when changing to text format.

Sub nowie()

Application.ScreenUpdating = False

range("A2").Activate

Columns("A:B").Insert Shift:=xlToLeft
ActiveCell.Offset(-1, 0).EntireRow.Delete

Columns("A:B").Select
Selection.NumberFormat = "General"

range("c1").Activate
ActiveCell.Value = range("c1")

Do
lancuch = ActiveCell.Value
rok = Mid(lancuch, 1, 9)

ActiveCell.Offset(0, -1).Select
ActiveCell.Value = rok

ActiveCell.Offset(0, -1).Select
ActiveCell.Value = "0W20H11"


ActiveCell.Offset(0, 2).Select
lancuch2 = ActiveCell.Value
czas = Mid(lancuch2, 11, 5)

ActiveCell.Value = "0" & czas


ActiveCell.Offset(1, 0).Select

Loop Until IsEmpty(ActiveCell.Offset(0, 1))


End Sub

[Bob Phillips]

Just use

=TEXT(LEFT(A2,8),"000000")

and

=TEXT(MID(A2,9,6),"000000")

[ssanguszko]

ok, but this should be repeated for thousands of rows ;), so VBA code needed

[Bob Phillips]

No, copy the formulae down.

[Bob Phillips]

Or write some VBA to enter the formula in all rows.

[ssanguszko]

Unfortunatelly this solutions does not work. I guess there is something with cells format.

I have managed to get separate values for date like 20110202 and time like 083133 (hh:mm:ss) but when there is "0" in the joint number, then I get 08313E for example. And there should be 0 instead of E. I dont know why this is happening. Maybe Excel sees the numbers converted to text in other way.

[ssanguszko]

here is the file - to see on Your own eyes how this looks like

//hp://depositfiles.com/en/rmv/104892653772884d

VBA code not included because is a little bit messy right now

[Bob Phillips]

Wrong URL, but data has been deleted and is unavailable.

[ssanguszko]

Excuse me, my mistake ;)

here it is:

ht//depositfiles.com/files/86haezpfb
just the data in column A should be divided into data YMD and time so for example : 20110302 and 083001 . WHat is important i need 0 at the beginning of time. I guess there is some problem with formatting.
There are only few rows in the file. Original one consist of up to 65000 rows///

[Bob Phillips]

Public Sub ProcessData()
Dim Lastrow As Long

Application.ScreenUpdating = False

With ActiveSheet

Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Columns("A:B").Insert
.Range("A1").Resize(Lastrow).Formula = "=TEXT(LEFT(C1,8),""000000"")"
.Range("B1").Resize(Lastrow).Formula = "=TEXT(MID(C1,9,6),""000000"")"
End With

Application.ScreenUpdating = True
End Sub

[ssanguszko]

thank You, it works like a charm