I created a .csv file that looked like this:

'1234,'1234,'2345,'2345
'1234,'1234,'2345,'2345
'1234,'1234,'2345,'2345
'1234,'1234,'2345,'2345
'1234,'1234,'2345,'2345
'1234,'1234,'2345,'2345

And this worked ok:

Option Explicit
Sub testme02()

Dim myCell As Range
Dim myRng As Range

With ActiveSheet
Set myRng = Nothing
On Error Resume Next
Set myRng = .Cells.SpecialCells(xlCellTypeConstants, xlTextValues)
On Error GoTo 0
End With

If myRng Is Nothing Then
MsgBox "No text values found"
Exit Sub
End If

For Each myCell In myRng.Cells
With myCell
If Left(.Value, 1) = "'" Then
.NumberFormat = "@"
.Value = Mid(.Value, 2)
End If
End With
Next myCell

End Sub


I created a .csv file that looked like this:

'1234,'1234,'2345,'2345
'1234,'1234,'2345,'2345
'1234,'1234,'2345,'2345
'1234,'1234,'2345,'2345
'1234,'1234,'2345,'2345
'1234,'1234,'2345,'2345



ras wrote:

When I prefix a number in a CSV with a single quote, the single quote appears
in the cell. If I then select the cell, put my cursor in the edit bar and
hit tab, it'll reinterpret the quote and treat the cell as text.

Is there any way to write a macro that visits each cell and does this? (No,
recording this into a macro does no good.)

This seems to be a common problem- and no, treating it as a txt file and
specifying all the formats is not a decent solution. I could write a perl
script to do this (or presumably a Basic script), but that seems like a lot
of work for a simple problem.

--

Dave Peterson