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 in Database by Edureka
• 13,640 points
141 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 in Database by gaurav
• 18,960 points
76 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 in Database by Neha
• 8,920 points
653 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 in Database by gaurav
• 18,960 points
84 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 in Database by gaurav
• 18,960 points
565 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 in Database by gaurav
• 18,960 points
412 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 in Database by gaurav
• 18,960 points
388 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 in Database by Edureka
• 13,640 points
141 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 in Database by gaurav
• 18,960 points
227 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 in Database by gaurav
• 18,960 points
144 views
webinar REGISTER FOR FREE WEBINAR X
Send OTP
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP