removing a prefix

  • Thread starter Thread starter Support
  • Start date Start date
S

Support

Hi,
I have imported some telephone call data for our organisation.
All the phone numbers appear as normal ie 01234567890 except for some which
are prefixed by 162 (this is a necessary part of our setup)
so that they appear 16201234567890. Is there any way of deleting the 162
part? I have tried a find and replace but didn't have the correct results.
thanks
 
the problem with using "find/replace" is that if you have 162 INSIDE the
number, it'll also get replaced
lets say 162891829 ---> 891829
but 162123162---> 123

Assuming that all the phone numbers are the same length (01234567890)
Then what you can do is:
Cell A1 : 16201234567890
Cell B1 : =RIGHT(A1,11)
or whichever number of digits your phonenumbers have instead of the "11"
If the phonenumbers don't have the 162, it'll work as well because it'll
still be 11 digits from the right.
If your phone number have different lengths, then it'll get a little more
complicated

guillermo
 
Hi

the best way is to use a helper column (ie a blank column adjacent to the
original data) and put the following formula in it (assuming your data is in
column A starting at row 2)
=IF(LEFT(A2,3)="162",RIGHT(A2,LEN(A2)-3),A2)
then copy this down as far as needed
now select the column and copy it
then select column A and choose edit / paste special - values
and you have the data in the format you want.

Hope this helps
Cheers
julieD
 
try this or the other

Sub remove162()
For Each c In Selection
'c.Value = Format(Right(c, Len(c) - 3), "00000000000")
c.Value = Right(c, 11)
Next
End Sub
 
You might prefer it with the IF. You don't say the length of the numbers.

Sub remove162()
For Each c In Selection
If Left(c, 3) = "162" Then
'c.Value = Format(Right(c, Len(c) - 3), "00000000000")
c.Value = Right(c, 11)
End If
Next
End Sub
 
Thanks all for your help - I have ended up with Julies suggestion as it
seemed the easiest and also allowed for different length phone numbers
Cheers
 
Back
Top