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.



Wednesday, 27 February 2013

Firefox OS

A big thank you to Mozilla for the invitation to Mozilla UK HQ in January 2013. The office in 101 St Martin’s Lane, London is pretty cool whereby Mozilla really spoil the developers by giving them great stuff - all on their account! It was an eye opening event whereby developers from Italy, France, Germany, Switzerland, India, Pakistan and surprisingly I am the only person from New Zealand (Ocenia). We were given session on Firefox OS and later on we were given a Developer phone for us to develop apps for them. Stay tune for more exciting thing that I will be developing over the next few months. If you are keen to get a phone from them, please do not forget to register your interest. You can get more information from here

Saturday, 14 July 2012

Cheapest app in Swedish / Svenskt


Swedish / Svenskt
Billigaste räknaren Billigaste är helt enkelt den bästa följeslagaren när du går livsmedel shopping. Den hjälper dig att spara pengar genom att beräkna den genomsnittliga kostnaden per enhet. billigaste, kalkylator, spara pengar, livsmedelsbutiker, shopping Go back to main Cheapest app page.

Cheapest app in Spanish / español


Spanish / español
Mejor calculadora Mejor es simplemente la mejor compañera cuando vaya de compras comestibles. Le ayuda a ahorrar dinero mediante el cálculo del costo promedio por artículo. más barata, calculadora, ahorrar dinero, tienda de comestibles, tiendas Go back to main Cheapest app page.

Cheapest app in Russian / русский


Russian / русский
Самый дешевый калькулятор Самый дешевый просто лучшим компаньоном, когда Вы идете продукты магазинам. Это поможет вам сэкономить деньги путем расчета средней стоимости за единицу. дешевый, калькулятор, сэкономить деньги, продуктовые, магазины Go back to main Cheapest app page.