VBA String sanitation

Question

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?

Answer 1

Excel has it's own replace function:

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

Answer 2

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

Answer 3

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