VBA String sanitation

I get data imported from an Oracle database and use Excel to create some reports from that. Recently one of our data entry folks has started beginning her entries with "+". Excel than evaluates that and adds = then displays the dreaded ?name# error. The error is error 2029.

I tried using

If IsError(C.Value) Then
    C.Value = Replace(C.Value, "=+", "", 1, -1, vbTextCompare)
End If

But that isn't getting me anywhere.

Any suggestions?

From stackoverflow

• Excel has it's own replace function:

  ActiveSheet.Cells.Replace What:="=+", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
  

  CABecker : When I run that I get runtime error '438' obeject dosen"t support this property or method

  DJ : oops - try again with the "Cells" - which returns a range object of all the cells in the worksheet

• If you have the data in a text/CSV file then you can try: Data > Import External Data > Import Data This launches a wizard which lets you specify specific columns as text and that causes symbols like +, - etc not to be parsed by Excel

  In VBA this can be done through the Worksheet("foo").QueryTables.Add method. There are quite a few options but the TextFileColumnDataTypes property lets you specify which columns to treat as text. It might be easier to work out what is happening by recording a macro of the wizard being used and then editing that to suit

  If you're reading in the data more directly (e.g. via ADO or DAO) then just make sure the destination cell is in text format before the data is added

  Worksheet("foo").Cells(r, c).NumberFormat = "@"

  ---

  NB in your original solution, you almost certainly wanted to look at C.Formula rather than C.Value If a cell C had a formula of =123+456 then C.Value would be 579 whereas C.Formula would be =123+456

  +1 on using the built-in Replace method in this context

  CABecker : Thank you barrowc, your final answer turned the trick, I forgot about looking at the .formula rather than the .value

• Just one other thing too, it should be:

  If IsError(C.Value) Then
      C.Value = Replace(C.Formula, "=+", "", 1, -1, vbTextCompare)
  End If
  

  This is because the =+ is stored in the formula property, not the value...You can assign the replace(...) to C.Value or C.Formula if you don't want to do the cleanup yourself in VBA