find undeclared named range (text) in formula

[MalFr]

Hi All,

I need to collect the data from a couple of cells in multiple CSV files.
Generally no problem, except that in some cases the (text) data in one of the cells in some of the sheets is prefixed by a minus sign.
This means that when excel opens the CSV sheet, any text in the cell after the "-" is considered as a named range and as this new range hasn't been declared or set up in any way, excel sees this as an error and shows the cell as [#NAME?].
Excel has also helpfully added an equals sign in front of the minus, which I think is compounding the problem.
Typically, the data I'm after may be of the format "-TEST-32"

If I go to the cell I can see the text in there so the content still exists but I can't get at it, only the [#NAME?] result.
I can trap for this, but its really better if I can get to this data with VBA.
Is there any way I can do this? Perhaps tell excel that this cell is not a formula? Or just get the content of the "formula" itself?

Cheers,

Mal

 

[oldbrewer]

can you find "-" and replace with " " (ie a blank character)

 

[MalFr]

That sounds like a good idea, i'll give it a try and let you know...

Mal

 

[MalFr]

Excellent, thanks, it worked.
Must remember that for the future...

Cheers,

Mal

 

[oldbrewer]

make sure any "needed" dashes have not been obliterated.

 

[MalFr]

Yup, already taken care of.
I changed it to [underscore], and as it's only a text label in a single cell on sheets from legacy systems it's a fairly easily contained situation, once you know how....

Thanks again,

Mal

 

[oldbrewer]

this forum keeps my 70 year old brain from attrifying.....