Telepone Number As String, Compare and Strip to Area Code

[mac7attack]

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

[mac7attack]

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

[Brandtrock]

Welcome to the Forum!!!

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,

[mac7attack]

Thanks a bunch HTH

mid and select case made it work wonderfully