Here is my problem, I am importing a CSV file that somewhere along the line got messed up. An alpha field managed to get a leading hyphen (ex: "-John Q Public"). When it gets imported, excel thinks it is a numeric value add an equals sign in front. Now the cell is an error (#NAME?). How can I programmatically fix the cell by removing the "=-"? I can identify it using IsError(rngCel.value), but I can not seem to modify the contents. Any suggestions?

"pb" wrote:
Here is my problem, I am importing a CSV file that somewhere
along the line got messed up. An alpha field managed to get
a leading hyphen (ex: "-John Q Public"). When it gets imported,
excel thinks it is a numeric value add an equals sign in front.
Now the cell is an error (#NAME?). How can I programmatically
fix the cell by removing the "=-"? I can identify it using
IsError(rngCel.value), but I can not seem to modify the contents.

It sounds like you are __opening__ the CSV file, not __importing__ it per
se.

One suggestion: use the External Data Import Text wizard to truly
__import__ the file. Then in the final menu, you might be able to select
type Text for the entire column that contains names.

That would avoid the problem altogether. I cannot give you step-by-step
instructions because you neglected to say what version of Excel you are
using.

If the Import Text wizard does not work for you (for example, you cannot
make the entire column Text because it contains a mix of data), then you can
use the following paradigm:

If IsError(rngCel.Value) Then
rngCel.Value = "'" & Mid(rngCel.Formula, 2)
End If

In case the string constant is difficult to read in your font, that is
double-quote single-quote (aka apostrophe) double-quote.

joeu2004,

Thanks for the help. I forgot to check rngCol.Formula.
All I needed was: rngCol.Value = Mid(rngCol.Formula, 3)

You are right, I am opening the CSV then fix the data before saving it. I have to do it this way because there are fields with imbeded CR, LF and Tab characters in them that really mess it up when I import it.