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.

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

"Rod Bowyer" wrote:

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.

Hi Mike,

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

Your code works perfectly,


"Mike" wrote:

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

"Rod Bowyer" wrote:

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.

Your Welcome

"Rod Bowyer" wrote:

Hi Mike,

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

Your code works perfectly,


"Mike" wrote:

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

"Rod Bowyer" wrote:

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.