Require a custom format for multiple layout of phone numbers

Question

I have a list of phone numbers with various layouts (Australia) and I need to create consistency for them. I need the layouts to look like the examples below. The data is then being imported back into a database but they are not split into different cells for each number formula.

For example i have - 5382 1111, 03 5382 1111, 0417 111 111, 1300 123 123, 13 13 13,

I have tried a few different formulas through the custom format cells but they are not working correctly and numbers are being deleted from their cells.

Answer 1

You can use custom number formats to achieve this.

For numbers beginning with 0, also watch for preceding the data with an apostrophe (') or making the whole column text format.

If you show what you tried, we can help troubleshoot.

An approach

Hint: to incorporate landline and mobile in the same rule, try conditional custom number formats. For more details see Custom number formats and how to use them.

An example

You could try using this custom number format string:

[<100000000]#### ####;[<400000000]0# #### ####;0### ### ###

Notes on this example:

• It works just fine with local landline and mobile numbers where you are.
• It will even work with customers from out of the area, including interstate customers.
• International numbers would be troublesome though and would need a different approach.
• If you use it with any numbers that begin with a + sign and the country code, it won't work and would need a different approach.

Assumptions about your data

The above example assumes:

• None of your inputs are text, they are all numbers
• The leading zeroes can be either included or omitted and it should still work
• All of your shortened local numbers are 8 digits (starting with any numeral, for example 5)
• All of your full local or interstate numbers are 10 digits (starting with 0)
• All of your mobile numbers are 10 digits (starting with 04)

Limitations

The above solution example does not deal with 1300 or 13 numbers properly:

• 13 numbers come out like 13 1313 instead of 131 313 or 13 13 13. Is this acceptable or do you need them like 13 13 13? If you do, what about ones that are quoted like 131 313? (automatically doing like 13 13 13 or 131 313 based on digit groupings would be a whole other complication).
• 1300 numbers come out like 01300 000 000. This is not ideal, but the only issue is the extra unnecessary leading 0; the rest of the format looks right.
• What about 1800 numbers, do you need them too?

If you have numbers stored as text, or numbers beginning with a + and an IDD (international country) code, you will need a different approach.

If you have numbers stored in the old format from back when they were 6 digits starting with 8 (e.g. 821 111) with area code 053 (before it inherited the 03 code and moved the 53 into the start of the number), then:

• You will need a different solution
• Your data is more than 3 times the age of this site