find undeclared named range (text) in formula

MalFr

New Member
Joined
Apr 10, 2015
Messages
32
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
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
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
 
Upvote 0

Forum statistics

Threads
1,217,357
Messages
6,136,080
Members
449,989
Latest member
chrisgarcia78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top