Hi everyone,

I'm a little stuck and desperate for advice! My brain has turned to
mush so now I turn you all for help. :)

I have a reference number in a receipting system. eg jd1407051455

The number is made up of two letters (initals of person), date (ddmmyy)
and 24 hour time (hhmm). Using VBA I have managed to split the number up
so that each piece of information sits in it's own column. (ie Column A
= name, B = date, C = time). This was done by copying, pasting and then
deleting irrelevant characters.

I now need to use VBA to insert the seperators so that excel recognises
these numbers as dates and times respectively.

eg. Instead of 140705 in a cell, I need 14/07/05 and instead of 1455 I
need 14:55.

I am at a complete loss as to how this can work. Just to throw a
spanner in the works - I need to somehow make it work on a complete
range (up to 1,000 rows of data) not just single cells.

If anyone has any ideas...I will be eternally grateful!

Thank you

sd


--
systematic
------------------------------------------------------------------------
systematic's Profile: http://www.excelforum.com/member.php...o&userid=25294
View this thread: http://www.excelforum.com/showthread...hreadid=491029

One way:

Public Sub ParseReceipt()
Dim rCell As Range
With Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
.TextToColumns _
Destination:=.Cells(1), _
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 2), Array(2, 4), Array(8, 1))
.Offset(0, 1).Resize(,1).Numberformat = "dd/mm/yy"
With .Offset(0, 2).Resize(, 1)
.NumberFormat = "hh:mm"
For Each rCell In .Cells
With rCell
.Value = TimeSerial( _
Int(.Value / 100), .Value Mod 100, 0)
End With
Next rCell
End With
End With
End Sub


In article ,
systematic
wrote:

Hi everyone,

I'm a little stuck and desperate for advice! My brain has turned to
mush so now I turn you all for help. :)

I have a reference number in a receipting system. eg jd1407051455

The number is made up of two letters (initals of person), date (ddmmyy)
and 24 hour time (hhmm). Using VBA I have managed to split the number up
so that each piece of information sits in it's own column. (ie Column A
= name, B = date, C = time). This was done by copying, pasting and then
deleting irrelevant characters.

I now need to use VBA to insert the seperators so that excel recognises
these numbers as dates and times respectively.

eg. Instead of 140705 in a cell, I need 14/07/05 and instead of 1455 I
need 14:55.

I am at a complete loss as to how this can work. Just to throw a
spanner in the works - I need to somehow make it work on a complete
range (up to 1,000 rows of data) not just single cells.

If anyone has any ideas...I will be eternally grateful!

Thank you

sd

Thank you so much for this! It (almost) does everything I need.

It works perfectly on the time column, but with the date not quite.

What is will do is convert a date serial number to the correct date (eg
if the number is 1 it will convert it to 1 Jan 1900). However the dates
in the reference number are not serial numbers.

ie. 140704 is 14/07/04.

Any ideas how to work around this?

Thanks again.

sd


--
systematic
------------------------------------------------------------------------
systematic's Profile: http://www.excelforum.com/member.php...o&userid=25294
View this thread: http://www.excelforum.com/showthread...hreadid=491029