Telepone Number As String, Compare and Strip to Area Code - Excel Help & Excel Macro Help
SEARCH ENTIRE SITE LOADING..

OZGRID Excel Help & Excel Best Practices Forums

Information Helpful? Why Not Donate.

SPECIALS PAGE FOR BARGAINS | BUILD YOUR GOLF SWING | FREE CUSTOM FUNCTIONS ADD-IN


Download Active Data For Excel Demo


Go Back Excel Help & Excel Macro Help > HELP FORUMS > EXCEL HELP
HOME Register Forum Help Calendar Search For Today's Posts Mark Forums Read

Reply

Telepone Number As String, Compare and Strip to Area Code

ANSWERS TO SIMILAR QUESTIONS
Compare and find a stringUse number from text string in code?Convert a number to string and add to stringExtract number from a mixed number and text string



Javascript DHTML Drop Down Menu Powered by dhtml-menu-builder.com

Create Excel dashboards quickly with Plug-N-Play reports.


 
Thread Tools Search this Thread
Old July 16th, 2005
Intelligent Converters
FREE DOWNLOADS
File/Password Recovery
mac7attack mac7attack is offline
I agreed to these rules
 
I'm a Spammer:
MS Office Version: 2003
Op System: Windows XP
Assumed Experience: Just Above Average (Can Record Macros & Know Most of Excel)
Join Date: 16th July 2005
English is 1st Language:
Posts: 3 -- Threads: 1
Telepone Number As String, Compare and Strip to Area Code

I am working on a macro that will take a list of phone numbers from a report and strip them down to the area code or international code. The major problems are as follows:

1) Telephone numbers are stored as strings because of how they are imported in to Excel
2) Not all telephones numbers are reported correctly. Some numbers will be reported as "Unknown", "408" or missing the last 2-4 digits. Others numbers will be only 4 digits; these are internally dialed numbers therefore digits will be changed to area code 919.
3) Most numbers will have "81" as the first two characters then the area code.

So examples of what the cells would contain and what result of macro should be:
Type of Call Cell Value Result
External Call 819196772499 919
International Call 8011498946261990 011
Internal Call 2466 919
Only Area Code 408 408
Unknown Unknown Unknown

Is there a way to compare the string to "Unknown"? and then Count how many digits there are and strip digits off both sides (if needed)?

Thanks in Advance
MAC
Print [Post / Thread] Reply With Quote
Old July 16th, 2005
Intelligent Converters
FREE DOWNLOADS
File/Password Recovery
mac7attack mac7attack is offline
I agreed to these rules
 
I'm a Spammer:
MS Office Version: 2003
Op System: Windows XP
Assumed Experience: Just Above Average (Can Record Macros & Know Most of Excel)
Join Date: 16th July 2005
English is 1st Language:
Posts: 3 -- Threads: 1
Re: Telepone Number As String, Compare and Strip to Area Code

Clarfication:
The cell would contain either a number or "Unknown". Thats it.
Comparing and striping these numbers will not be the only thing the macro is doing but it is the only thing I having trouble with.
Thanks
MAC
Print [Post / Thread] Reply With Quote
Old July 16th, 2005
Brandtrock's Avatar
Brandtrock Brandtrock is offline
Long Term Member
 
I'm a Spammer:
MS Office Version: Excel 97, 2000, 2003
Op System: Windows XP
Assumed Experience: Expert (Know VBA Well and Use It & Excel Almost Da
Join Date: 20th May 2003
English is 1st Language: Yes
Location: Mesa, Az
Posts: 1,116 -- Threads: 34
Re: Telepone Number As String, Compare and Strip to Area Code

Welcome to the Forum!!!

Quote:
Originally Posted by mac7attack
So examples of what the cells would contain and what result of macro should be:
Type of Call Cell Value Result
External Call 819196772499 919
International Call 8011498946261990 011
Internal Call 2466 919
Only Area Code 408 408
Unknown Unknown Unknown

HINT:
External call: Length of cell value is 12, you could use MID(cell ref,3,3)
Int'l call: Length of cell value is 14, you could use MID(cell ref,2,3)
Internal call: Length of cell value is 4, you could use "919"
Only area code: Length of cell value is 3, you could simply return the cell ref value
Unknown: Length of cell value is 7, you could simply return the cell ref value

Using the HINT from above, you could use a nested IF, or more preferrably, a SELECT CASE in your code to return the values to the appropriate cells. Of course, the inclusion of the TRIM function to eliminate any unnecessary spaces would also help.

HTH,
Print [Post / Thread] Reply With Quote
Old July 17th, 2005
Intelligent Converters
FREE DOWNLOADS
File/Password Recovery
mac7attack mac7attack is offline
I agreed to these rules
 
I'm a Spammer:
MS Office Version: 2003
Op System: Windows XP
Assumed Experience: Just Above Average (Can Record Macros & Know Most of Excel)
Join Date: 16th July 2005
English is 1st Language:
Posts: 3 -- Threads: 1
Re: Telepone Number As String, Compare and Strip to Area Code

Thanks a bunch HTH

mid and select case made it work wonderfully
Print [Post / Thread] Reply With Quote
Reply Lifetime Upgrade To Ad Free Styles

   « PREVIOUS Addin debugging || Using Solver for Product Distribution NEXT »
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump


All times are GMT +9. The time now is 23:58.


Powered by vBulletin Version 3.5.4
Copyright ©2000 - 2010, Jelsoft Enterprises Ltd.
Ozgrid is Not Associated With Microsoft. Ozgrid Retains the Rights to ALL Posts and Threads