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:

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