VBA String sanitation

Question

I get data imported from an Oracle database and use Excel to create some reports. Recently, one of our data entry users began to enter their entries using "+". Excel, how does this evaluate, and add = then displays an error with an error? Name #. Error - error 2029.

I tried to use

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

But it won’t deliver me.

Any suggestions?

Answer 1

If you have data in a text / CSV file, you can try: Data > Import External Data > Import DataThis launches a wizard that allows you to specify specific columns as text and that calls characters like +, -, etc. not processed by Excel

VBA Worksheet("foo").QueryTables.Add. , TextFileColumnDataTypes , . , , , , ,

(, ADO DAO), , .

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

---

NB C.Formula, C.Value C =123+456, C.Value 579, C.Formula =123+456

+1 Replace

Answer 2

Excel :

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

Answer 3

, :

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

This is because value = + is stored in the property of the formula, and not in the value ... You can assign a replacement (...) to C.Value or C.Formula if you do not want to clean yourself in VBA