CODEWITHSUNDEEP
excelexcel-formulaformattingoffice365string-formatting
Styff
Styff

Reputation: 113

EXCEL - How to create a custom cell format for an Australian phone number: xxxx-xxx-xxx

Australian mobile phone (cell phone) and land line phone numbers are all 10 digit numbers which all begin with a zero.

When I enter the phone number into an Excel cell formatted as a Number it drops the leading zero. e.g. 0412555666 becomes 412555666. Formatting the cell as Text keeps the leading zero.

I would like a user to be able to enter a phone number, keeping the leading zero intact, and have the format displayed as e.g. '0412-555-666'

I have attempted the custom format 0####"-"###"-"### however the format fails if the cell is Text and if I change it to a Number then the leading 0 is removed from the input.

P.S. I'm aware that I can add another Field to the spreadsheet with the formula =IF(LEN(A1)=10,CONCATENATE(MID(A1, 1, 4), "-", MID(A1, 5, 3), "-", MID(A1, 8, 3)), "Invalid Number") but I just want to change the way the cell is displayed, not its contents.

Upvotes: 0

Views: 7211

Answers (3)

Sandy Mackenzie
Sandy Mackenzie

Reputation: 1

All that for a simple format? I am not an expert, but this is the format that works for the 0412 555 666 = 0### ### ###

I just need to work out how to get the format for Australian landlines.

07 5587 5869 or (07) 5587 5869

Nobody has answered how to do that without breaking your head with the above formulas

Upvotes: 0

Error 1004
Error 1004

Reputation: 8230

Instead of writing "0412555666" in the cell you can write "'0412555666" (add ' before the number) which also keeps zeros and in the next cell import:

=LEFT(A1,4) & "-"  & MID(A1,5,3) & "-" & RIGHT(A1,3)

Image show:

enter image description here

Upvotes: 0

user10808941
user10808941

Reputation:

Custom number formatting can properly determine the correct land/mobile number formatting as long as you are willing to input at least 9 digits of the AU 10 digit Full National Number (FNN).

Select the column to receive the phone numbers and tap Ctrl+1. Move to the Numbers tab and select Custom from the list down the left. Use the following for type.

[>600000000]00 0000 0000;[>400000000]0000 000 000;00 0000 0000;[red]@

Mobile phones will be in the XXXX XXX XXX format, land lines will be in the XX XXXX XXXX format and any rogue text input will be colored red.

If you are using VBA to send these to a modem or some other dialer, use the cells .Value or .Value2 property. If you require the formatted phone number for a report or something similar, retrieve it using the cells .Text property.

enter image description here

Upvotes: 3

Related Questions