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

0 votes
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.
Apr 7, 2022 in Database by Edureka
• 13,670 points
616 views

No answer to this question. Be the first to respond.

Your answer

Your name to display (optional):
Privacy: Your email address will only be used for sending these notifications.

Related Questions In Database

0 votes
1 answer

How to convert a column number (e.g. 127) into an Excel column (e.g. AA)

If anyone needs to do this in ...READ MORE

answered Mar 30, 2022 in Database by gaurav
• 23,260 points
689 views
0 votes
2 answers
0 votes
1 answer

How to generate the statement CREATE TABLE for an existing table in postgreSQL?

Generate table creation statement for an existing ...READ MORE

answered Feb 9, 2022 in Database by Neha
• 9,060 points
5,851 views
0 votes
1 answer

Convert a number to a letter in C# for use in Microsoft Excel [duplicate]

If you are familiar with using formulas ...READ MORE

answered Feb 23, 2022 in Database by gaurav
• 23,260 points
571 views
0 votes
1 answer

How to get the "Name Box" name of an Excel cell?

By typing in the Name Box, you ...READ MORE

answered Mar 15, 2022 in Database by gaurav
• 23,260 points
3,868 views
0 votes
1 answer

How to use xlrd for writing an excel file

In Python, you can read and write ...READ MORE

answered Mar 30, 2022 in Database by gaurav
• 23,260 points
2,511 views
0 votes
1 answer

How to translate a BDS excel-formula to XBBG API for Python

The parameters are improperly specified by you. ...READ MORE

answered Apr 1, 2022 in Database by gaurav
• 23,260 points
1,857 views
0 votes
1 answer

How to create an excel file in android?

In Android, create Excel XLSX or XLS ...READ MORE

answered Apr 4, 2022 in Database by Edureka
• 13,670 points
1,003 views
0 votes
1 answer

How to deal with "Microsoft Excel is waiting for another application to complete an OLE action"

The first step in troubleshooting is to ...READ MORE

answered Apr 6, 2022 in Database by gaurav
• 23,260 points
1,131 views
0 votes
1 answer

How to run a SQL query on an Excel table?

On Excel tables, how to construct and ...READ MORE

answered Apr 11, 2022 in Database by gaurav
• 23,260 points
737 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP