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
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

MalFr

New Member
Joined
Apr 10, 2015
Messages
32
That sounds like a good idea, i'll give it a try and let you know...

Mal
 

MalFr

New Member
Joined
Apr 10, 2015
Messages
32
Excellent, thanks, it worked.
Must remember that for the future...

Cheers,

Mal
 

oldbrewer

Well-known Member
Joined
Apr 11, 2010
Messages
11,005

ADVERTISEMENT

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

MalFr

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

Watch MrExcel Video

Forum statistics

Threads
1,123,531
Messages
5,602,209
Members
414,513
Latest member
junbuggle

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
Top