Posts

Showing posts from July, 2015

Transforming phone number with alphabet into numeric phone number in Excel

Image
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"),&