Macro to Change Changing Date Format Data to Text

[Guest]

I am importing data from a website. The field is position and number of
entries (for example if you came third out of 12 entries it is shown as
3/12). Excel imports this field as a date but I need it as text to work on
it.

I can convert to text by simply editing and putting a ' at the start of the
cell but I need to have a macro to do this each time I bring in this field
(number of entries can vary).

I can write a simple "for next" loop macro to edit and insert the ' but it
wipes all the other data out. How can I write a macro to edit the cell by
placing a ' at the start of the cell to convert to text but leave the data as
it is imported by Excel?

Any help would be gratefully received.

 

[Guest]

Sub trythis()
lastcell = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To lastcell
Range("A" & i).Value = "'" & Format(Range("A" & i).Value, "m/dd")
Next
End Sub

 

[Guest]

Hi Mike,

I really appreciate this (has frustrated me for some time trying to write
this).

Your code works perfectly,

 

[Guest]

Your Welcome

Hi Mike,

I really appreciate this (has frustrated me for some time trying to write
this).

Your code works perfectly,