Manufacturing Information Solutions Forum Index Manufacturing Information Solutions
Your Place for Support and Discussions
 
 FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Clean Up Phone Number

 
Post new topic   Reply to topic    Manufacturing Information Solutions Forum Index -> Microsoft Excel
View previous topic :: View next topic  
Author Message
LindaSimpson
New Member


Joined: 21 May 2007
Posts: 24
Location: Cleaveland, IH

PostPosted: Wed Feb 23, 2011 12:10 pm    Post subject: Clean Up Phone Number Reply with quote

Clean up Incorrectly Formatted Phone Numbers using Excel
Source:

http://chandoo.org/wp/2008/09/30/clean-up-incorrectly-formatted-phone-numbers-using-excel/

In our Utopian imaginations all the data would have been standardized and shareable across systems and people. But alas, the reality is totally different. We seldom get data in the format / way we desire it to be. In other words, the ingredients are all there, but for us to prepare the dinner, you must pre-process them.

Often this pre-processing or cleaning up the data takes quite an amount of time it self leaving very little to do the actual work. That is when you can use excel?s powerful data cleaning techniques to handle the situations.

One common problem with corporate data is incorrectly formatted phone numbers. Most of us are used to a standard 10 digit phone number format like 123-123-1234 or (123) 123 1234, but when you get that customer data, very few phone numbers in it are formatted like above. Instead you might see phone numbers like 1231231234, 12312 31234, (123)123-1234 etc.

It is not really difficult to clean up the phone numbers if we know before hand how they are formatted. For eg. you can easily convert a phone number like 1231231234 to 123-123-1234 using excel text formatting functions like =TEXT(1231231234,"000-000-0000"). But it is a rare case in which we have control over the incoming format and quickly you will have to use a slew of format / text processing functions to clean up the data.

To simplify the whole thing, I have written a small VBA UDF (User Defined Function) which you can add to your excel add-ins list and use to clean up virtually any phone number format to standard phone number.

Code:
Function cleanPhoneNumber(thisNumber As String) As String
    ' this function aspires to clean any phone number format
    ' to standard format (+9999) 999-999-9999 or 999-999-9999
    ' works with almost all phone number formats stored in text

Dim retNumber As String

For i = 1 To Len(thisNumber)
    If Asc(Mid(thisNumber, i, 1)) >= Asc("0") And Asc(Mid(thisNumber, i, 1)) <= Asc("9") Then
        retNumber = retNumber + Mid(thisNumber, i, 1)
    End If
Next
If Len(retNumber) > 10 Then
    ' format for country code as well
    cleanPhoneNumber = Format(retNumber, "(+#) 000-000-0000")
Else
    cleanPhoneNumber = Format(retNumber, "000-000-0000")
End If
End Function



The above function is pretty straight forward and simple. It scans the input text for any numeric ASCII codes and saves them to another text field. Once the scanning is complete the function will format the final number to 999-999-9999 format if the number has 10 or less digits, otherwise to (+9999) 999-999-9999 format (with country code).
________
Expert insurance


Last edited by LindaSimpson on Wed Mar 16, 2011 11:44 pm; edited 2 times in total
Back to top
View user's profile Send private message
LindaSimpson
New Member


Joined: 21 May 2007
Posts: 24
Location: Cleaveland, IH

PostPosted: Wed Feb 23, 2011 2:19 pm    Post subject: How to use the function Reply with quote

You can install a vba user defined function (UDF) like this very simply. Just follow these steps:

1. In your workbook, right click on the sheet name and select view code
2. in the resulting window, click menu > insert > module
3. You will see a blank module (basically a place where you can write all your macros and udfs), copy paste the code from here to there
4. save by pressing ctrl+s
5. close it and return to your excel sheet
6. save the work book, just in case
7. Now use the cleanPhoneNumber() in your cells like you would use sum(), if() etc.


remember, using this method, the cleanPhoneNumber() will only work in that particular workbook. If you need to use it in all the workbooks, just save the file as ?Excel add in? type (menu > save as, and file type).
Now from tools > addins, click browse and point to the saved excel addin file. Once the file is shown, make sure you check it to install that add-in. This way, you can use cleanPhoneNumber() in all the workbooks.
________
Roll blunts
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    Manufacturing Information Solutions Forum Index -> Microsoft Excel All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group