title: Trouble replacing Chr(160) with VBA in excel
body: <p>I have been receiving excel files for awhile that are usually plagued with the special character alt+0160 after the accounts. I usually just manually replace it in excel but recently I've grown lazy and want to replace it using my VBA script. This script is used to insert the needed columns into our database:</p>
  
  <pre><code>Sub insert()
  
      Dim sSql As String
      Dim db As New ADODB.Connection 'Go to Tools, References and turn on ActiveX Data Objects 2.8 Library
      db.Open "DSN=XXXX;uid=XXXX;pwd=XXXX" 'INSERT ORACLE NAME AND PASSWORD
  
      For i = 2 To 92 'Change Rows where it starts and ends
  
              strAccount = Replace(Trim(Range("a" &amp; i).Text), Chr(160), "")
  
              If IsNumeric(strAccount) Then
                      While Len(strAccount) &lt; 8
                              strAccount = "0" &amp; strAccount
                      Wend
              Else
                      strAccount = UCase(strAccount)
              End If
  
      sSql = "insert into XXXXX ("
      sSql = sSql &amp; "    BATCH_ID"
      sSql = sSql &amp; "  , ACCOUNT "
      sSql = sSql &amp; "  , ATTORNEY_ID"
      sSql = sSql &amp; "  , ORG_ID"
      sSql = sSql &amp; "  , TRANSACTION_DATE"
      sSql = sSql &amp; "  , DATE_INSERTED"
      sSql = sSql &amp; "  , TRANSACTION_CODE"
      sSql = sSql &amp; "  , AMOUNT"
      sSql = sSql &amp; "  , DESCRIPTION"
      sSql = sSql &amp; "  , DEBTOR_SSN"
  
      sSql = sSql &amp; ") VALUES ("
      sSql = sSql &amp; "    (SELECT MAX(BATCH_ID) FROM XXXX)"
      sSql = sSql &amp; "  , '" &amp; strAccount &amp; "'"
      sSql = sSql &amp; "  , (SELECT ATTY_ID FROM XXXX WHERE BATCH_ID = (SELECT MAX(BATCH_ID) FROM XXXXX))"
      sSql = sSql &amp; "  , (SELECT ORGANIZATION_ID FROM XXXX WHERE BATCH_ID = (SELECT MAX(BATCH_ID) FROM XXXXX))"
      sSql = sSql &amp; "  , TO_DATE ('" &amp; Trim(Range("B" &amp; i).Text) &amp; "', 'MM/DD/YYYY') "
      sSql = sSql &amp; "  , SYSDATE"
      sSql = sSql &amp; "  , '" &amp; Trim(Range("D" &amp; i).Text) &amp; "'" 'CHANGE TO COLUMN TRANSACTION CODE IS IN
      sSql = sSql &amp; "  , '" &amp; Replace(Replace(Replace(Replace(Trim(Range("C" &amp; i).Text), "$", ""), ",", ""), ")", ""), "(", "") &amp; "'"
      sSql = sSql &amp; "  , '" &amp; Replace(Trim(Range("H" &amp; i).Text), "'", "''") &amp; "'"
      sSql = sSql &amp; "  , '" &amp; Replace(Replace(Trim(Range("F" &amp; i).Text), " ", ""), "-", "") &amp; "'"
  
      sSql = sSql &amp; ")"
  
  
      db.Execute sSql
  
      DoEvents
      Debug.Print i
  
      Next i
  End Sub
  </code></pre>
  
  <p>Now I did some research and found out that to replace these characters you use Chr(160). I have used that in my <code>Replace</code> but it does not seem to be doing the trick, the accounts still get uploaded with those terrible special characters. Any help would be greatly appreciated.</p>