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

PowerBuilder PBL Source Extractor

Image
You can use this PowerBuilder library to extract source code from PowerBuilder PBL and it is using PowerScript to extract the source. This PowerBuilder source extractor was originally developed for PowerBuilder 8 and recently migrated to PowerBuilder 12.6. If you like this PBL, let me know by giving your feedback. Head out to  https://github.com/peter1wong/pblSourceExtractor
Read more