Macro to select text and go to next line

[grabrail]

Hi

Im having a weird problem, I have copied around 2500 lines of dates into my spreadsheet, and some were in date format others were in general format. I have changed the format of the column to date, but the ones that were previously just text are staying formatted as text.

I've found if I select the cell, then place my cursor at the end of the the text and press enter, it then converts to date format.

Obviously to do this for 2500 lines would be time consuming.

Is there a way I can record or write a macro that will select each cell in turn to the end of the list, and literally just accept the value that is already in there, press return and move to the next line?

I tried recording a macro but when I look at the code, the value is showing as the value that is in the cell already, if I run this on every cell, it will change the value to that value.

example

Sub Macro1()'
' Macro1 Macro
'


'
    ActiveCell.FormulaR1C1 = "3/22/2017"
    Range("C3").Select
End Sub

 

[Norie]

Have you tried 'fixing' the dates by using Data>Text to columns...?

 

[KOKOSEK]

Hi

Just typing without any testing:

Sub ReValued()
For Each cell In [A1:A40]
    If cell.Value <> "" Then
        cell.Value = cell.Value
        cell.NumberFormat = "mm/dd/yyyy"
    End If
Next cell
End Sub

 

[sadboy309]

I don't see your data, so I try code as:

Sub ck_date()
Dim i As Long, lst As Long


lst = Range("C" & Rows.Count).End(xlUp).Row

For i = 2 To lst
Cells(i, 3) = DateValue(Cells(i, 3))
Next


End Sub

 

[grabrail]

Hi

I tried this and it worked for some cells but not others. Im baffled as the cells are all formatted in date format. And I can still go to the cells it hasnt worked on select value and press enter and it then formats correctly.

Hi

Just typing without any testing:

Sub ReValued()
For Each cell In [A1:A40]
    If cell.Value <> "" Then
        cell.Value = cell.Value
        cell.NumberFormat = "mm/dd/yyyy"
    End If
Next cell
End Sub

 

[grabrail]

Have you tried 'fixing' the dates by using Data>Text to columns...?

This worked thank you so much, ive only ever used the delimited version of this, but did this with fixed width and the options were there to fix it. Something so simple ! Doh

Thank you to all that replied

 

[Norie]

What happens if you select the column with dates, goto Data>Text to columns..., select DMY on the third step and click Finish?

Oh, you tried it.

 

[KOKOSEK]

Hi

I tried this and it worked for some cells but not others. Im baffled as the cells are all formatted in date format. And I can still go to the cells it hasnt worked on select value and press enter and it then formats correctly.

Let's convert date as text into number :

Sub ReValued()
For Each cell In [A1:A40]
    If cell.Value <> "" Then
        [B]cell.Value = cell.Value * 1[/B]
        cell.NumberFormat = "mm/dd/yyyy" 'or dd/mm/yyyy depends how you Windows like
    End If
Next cell
End Sub