ProofReader
New Member
- Joined
- Aug 22, 2008
- Messages
- 4
G'day! 
I have a simple spreadsheet which lists phone numbers and addresses. I'd like to be able to convert the phone numbers to an international dialling code in each instance. The problem is that the phone numbers are all entered in differing formats.
In Australia, the phone formats are as follows:
Country = 61
Area code - varies according to which of the eight states or territories it belongs, but range from 01 to 09. When dialling internationally, the leading 0 is dropped.
Landline phone = eight digits.
Mobile (cell) phone = ten digits all starting with 04. Again, the leading 0 is removed for international dialling.
I have already removed all leading 0s and any other symbols like brackets or dashes.
The phone numbers are all contained in columns B and C. I'd like to do a 'look up' of columns E (State) and G (Country) and base the conversion upon that information.
What I'd like to do is to convert them ALL to 11 digit numbers for international dialling. For example:
I can provide a sample of the various types of numbers which I'm encountering.
Unfortunately, it appears that I cannot attach this sample though. 
I'm hoping that someone can help me by creating a macro to sort and convert these numbers? I'd like a macro (if possible) because I'd like to use it with other spreadsheets yet to be created. You may, of course, know of a better way of doing this? Hoping that all makes sense! Can you help please?
I have a simple spreadsheet which lists phone numbers and addresses. I'd like to be able to convert the phone numbers to an international dialling code in each instance. The problem is that the phone numbers are all entered in differing formats.
In Australia, the phone formats are as follows:
Country = 61
Area code - varies according to which of the eight states or territories it belongs, but range from 01 to 09. When dialling internationally, the leading 0 is dropped.
Landline phone = eight digits.
Mobile (cell) phone = ten digits all starting with 04. Again, the leading 0 is removed for international dialling.
I have already removed all leading 0s and any other symbols like brackets or dashes.
The phone numbers are all contained in columns B and C. I'd like to do a 'look up' of columns E (State) and G (Country) and base the conversion upon that information.
What I'd like to do is to convert them ALL to 11 digit numbers for international dialling. For example:
Where column E = VIC, column B may contain the number set as a 312345678 or just 12345678 (the initial 3 is used to denote Victoria or VIC).
In this case, I need to convert the number to 61312345678. It need to recognise that VIC's are code is '3' and add it if it's missing. It also needs to add '61' for the country at the beginning, if it's not already there.
I can provide a sample of the various types of numbers which I'm encountering.
Unfortunately, it appears that I cannot attach this sample though. I'm hoping that someone can help me by creating a macro to sort and convert these numbers? I'd like a macro (if possible) because I'd like to use it with other spreadsheets yet to be created. You may, of course, know of a better way of doing this? Hoping that all makes sense! Can you help please?
