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.



Comments

Post a Comment

Popular posts from this blog

Introduction to Enyo Development for webOS

Image
Getting started from Mojo to Enyo can be quite disappointing. Looking at the Tutorial from HP , it is pretty hard and despite searching over the internet, I couldn't start it until I have done an experiment myself. Hope this will help someone and please do give your comment or feedback. From the beginning, I have generated a Basic Application called x3 from Eclipse. You can also do this using a plain text editor if you are not using any development tool. This is the file structure of the completed x3 application. You will find the next steps in the next paragraph. Next I have taken the original index.html and replace it with the following code. Note: The folder containing enyo.js must be a folder in your computer and this is an example of the SDK installed on Debian. If you are using Windows or Mac, I am sure you can enter similar information. I have put mine as follow. index.html: Next, I had a look at appinfo.json and added a new row "u...
Read more

Installing Skype on Pre 3 (AT&T)

Image
If you have a Pre 3 (AT&T) and wonder how you can add Skype to it, you may be able to get it done. AT&T was never launched, Verizon and the European version will have Skype built-in. I have tried searching for Skype in the App Catalog, unfortunately, I can't get it. Steps: 1. Download the webOS 2.2.0 for Unlocked Pre 3 from webOS Doctor. 2. Extract the content of the jar file. 3. Open the wr.tar file and the Skype app (com.palm.app.skype_1.1-8.3_armv7.ipk) is in there. 4. Set your device as USB device as you will need to copy Skype app into /media/internal/downloads folder. Once copied, eject the device. 5. Put your device into Developer mode. If you are using Debian/Windows, you can go into command-line. Whilst in command-line, type novaterm. Once your device is able to be recognised by your OS (Debian/Windows), you will see something like this: root@palm-webos:/media/internal/downloads# 6. You can then type the following in command-line: ipkg -force-de...
Read more