Hi all,

I am trying to create some code in order to remove characters from a phone number and convert into a string to place back into an excel database. The phone numbers have country codes attatched so only wish to remove all characters.

e.g of a phone number:
0773333333 |44|-2| @

There are over 5000 records of phone numbers so an =MID function is not possible. Any help would be greatly appreciated.

Many Thanks

If you have a limited set of characters to remove, I'd use Replace as follows (Assuming The # is in column B):

************************************************** *****************
dim sValue As String, iRowOn As Long
sValue = Replace(Cells(iRowOn, 2).value, "|", "")
sValue = Replace(sValue, "@", "")
'***Other characters to remove / string manipulations***
Cells(iRowOn, 2).value = sValue
************************************************** *****************


Otherwise you'll have to check character by character as such:
************************************************** *****************
dim iCharOn As Long, sValue As String, sChar as string

For iCharOn = 1 To Len(Cells(iRowOn, 2).Value)
  sChar = Mid(Cells(iRowOn, 2).Value, iCharOn, 1)
  If Val(sChar) > 0 Or sChar = "0" Then sValue = sValue & sChar
  '***Other logic processing of character can be added***
Next
Cells(iRowOn, 2).Value = sValue
************************************************** *****************

Hope one of these helps you.