Monday, 13 July 2015

Transforming phone number with alphabet into numeric phone number in Excel

Recently, there is a need to transform phone number which consists of alphabet in the phone number itself into a numeric phone number. This is quite a normal process when you try to import leads into Salesforce.

For example, 0800CATCHME to be transformed into 08002282463


The logic is to substitute "A", "B", "C" with "2"; "D", "E", "F" with "3" and so on. In addition, I have also transformed the " " space with a non-space.









The original value is in cell A2. Put the following command in cell B2:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"A","2"),"B","2"),"C","2"),"D","3"),"E","3"),"F","3"),"G","4"),"H","4"),"I","4"),"J","5"),"K","5"),"L","5"),"M","6"),"N","6"),"O","6"),"P","7"),"Q","7"),"R","7"),"S","7"),"T","8"),"U","8"),"V","8"),"W","9"),"X","9"),"Y","9"),"Z","9")," ","")



Please leave me a message or donate if you find this helpful. Thanks.



No comments:

Post a Comment