Getting data out of a cell with a NAME error in Excel VBA

Question

I import data into Excel from another software and manipulate it using a macro. Because the text in the other programme began with a hyphen, I recently witnessed a user experience a crash. It's a last name, and the value was "-Smith" when I would normally anticipate "Smith" or something similar. This was interpreted as an equation when exported to Excel, and the value in the cell was "=-Smith". Naturally, a #NAME? error was displayed. An error occurred when I attempted to set a variable to that value.

I can get around this by assigning the cell contents to a variable before utilising the ISTEXT() function. But what I really want to do is gather the information.

As a test, on the worksheet I tried this:

'''=IF(ISTEXT(A2)=FALSE,MID(A2,3,20),A2)

You would think that this would grab the "Smith" portion of the contents of A2, but it doesn't. I've also tried:

'''=VALUETOTEXT(MID(A2,3,20))
'''=TEXTAFTER(A2,"-")

Nothing works. I get #NAME? error for all of this. If I click on the cell I can see the data I need, but I can't figure out how to get it.

Answer 1

If you need VBA, use .Formula:

Dim f As String
f = ActiveCell.Formula

Which you can then clean:

f = Replace(f, "=-", "")

If you need a worksheet formula, then use FORMULATEXT:

=SUBSTITUTE(FORMULATEXT(A1),"=-","")