I have a general formated cell with the following in:-

DIN 060416 (the numbers change day to day of course)

In a Macro I need to remove the "DIN" part which I can do, but as soon as I
do this it alters the format to number and shows it 60416 which if you try
and date format is completely wrong.

I need it to be 06/04/16. Any help would be much appriciated and as you can
prob tell I am rubbish on Excel ;) Alastair.

You can probably adapt this for your needs

Dim tmp As String
With ActiveCell
tmp = Trim(Replace(.Value, "DIN", ""))
.Value = DateSerial(Right(tmp, 2), Mid(tmp, 3, 2), Left(tmp, 2))
End With


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Alastair79" wrote in message
...
I have a general formated cell with the following in:-

DIN 060416 (the numbers change day to day of course)

In a Macro I need to remove the "DIN" part which I can do, but as soon as
I
do this it alters the format to number and shows it 60416 which if you try
and date format is completely wrong.

I need it to be 06/04/16. Any help would be much appriciated and as you
can
prob tell I am rubbish on Excel ;) Alastair.

Is this one column?

If yes, record a macro when you select that column.
choose fixed width
draw a line right before the first digit (after the "din ")
Choose ignore (Do not import) the first field
choose ymd (or myd???) for the date portion

And plop it down directly in the original location.

Alastair79 wrote:

I have a general formated cell with the following in:-

DIN 060416 (the numbers change day to day of course)

In a Macro I need to remove the "DIN" part which I can do, but as soon as I
do this it alters the format to number and shows it 60416 which if you try
and date format is completely wrong.

I need it to be 06/04/16. Any help would be much appriciated and as you can
prob tell I am rubbish on Excel ;) Alastair.

--

Dave Peterson

=DATE(MID(A1,LEN(A1)-5,2),MID(A1,LEN(A1)-3,2),RIGHT(A1,2))

--
Kind regards,

Niek Otten

"Alastair79" wrote in message ...
|I have a general formated cell with the following in:-
|
| DIN 060416 (the numbers change day to day of course)
|
| In a Macro I need to remove the "DIN" part which I can do, but as soon as I
| do this it alters the format to number and shows it 60416 which if you try
| and date format is completely wrong.
|
| I need it to be 06/04/16. Any help would be much appriciated and as you can
| prob tell I am rubbish on Excel ;) Alastair.

Select the cells and run this macro.

Sub abd()
Dim dt As Date
Dim s As String, y As String
Dim m As String, d As String
For Each cell In Selection
s = Trim(cell)
If UCase(Left(cell, 3)) = "DIN" Then
s = Replace(UCase(s), "DIN ", "")
s = Replace(UCase(s), "DIN", "")
y = Mid(s, 1, 2)
m = Mid(s, 3, 2)
d = Mid(s, 5, 2)
dt = DateValue(m & "/" & d & "/" & y)
cell.Value = dt
cell.NumberFormat = "yy/mm/dd"
End If
Next
End Sub

--
Regards,
Tom Ogilvy


"Alastair79" wrote:

I have a general formated cell with the following in:-

DIN 060416 (the numbers change day to day of course)

In a Macro I need to remove the "DIN" part which I can do, but as soon as I
do this it alters the format to number and shows it 60416 which if you try
and date format is completely wrong.

I need it to be 06/04/16. Any help would be much appriciated and as you can
prob tell I am rubbish on Excel ;) Alastair.

Bob, this worked a treat after I swapped the Left and Right part around.
Thanks alot, Alastair.

"Bob Phillips" wrote:

You can probably adapt this for your needs

Dim tmp As String
With ActiveCell
tmp = Trim(Replace(.Value, "DIN", ""))
.Value = DateSerial(Right(tmp, 2), Mid(tmp, 3, 2), Left(tmp, 2))
End With


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Alastair79" wrote in message
...
I have a general formated cell with the following in:-

DIN 060416 (the numbers change day to day of course)

In a Macro I need to remove the "DIN" part which I can do, but as soon as
I
do this it alters the format to number and shows it 60416 which if you try
and date format is completely wrong.

I need it to be 06/04/16. Any help would be much appriciated and as you
can
prob tell I am rubbish on Excel ;) Alastair.