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.