• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course โ€“ Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel Custom Cell Formats

You are here: Home / Excel / Excel Custom Cell Formats
December 7, 2010 by Mynda Treacy

Learning how to customise a cell format in Excel allows you to not only format your data the way you want, but in some instances it can save you time.

Before we dive in you need to know that despite how the text appears after youโ€™ve set your custom cell format, the underlying value is unchanged for the purpose of formulas and calculations.

How to enter an Excel custom cell format

Select the cell/s you want to format then open the Format Cells window.

  • The quick way just press CTRL+1
  • Or the way most people do it is to right click and select โ€˜Format Cellsโ€™.
  • On the Number Tab select Custom from the Category list.
excel custom cell format dialog box

Note: Itโ€™s handy to have the text you want to format in the cell before you press CTRL+1 because Excel will give you a sample view of what the text is going to look like in the Format Cells window, so you can see before pressing OK, if itโ€™s what you want.

How to make your cell formats look the way you want

Custom Cell Formats Text Before Formatting Custom Format Formatted Text
Brackets for negative values -500 #,##0;(#,##0) (500)
Red and brackets for negative values -500 #,##0.00;[Red](#,##0.00) (500.00)
Day of the week in full 27/03/2010 dddd Saturday
Day, date, month and year 27/03/2010 ddd dd mmm yyyy Sat 27 Mar 2010
Month 27/03/2010 mmmm March
Phone Numbers 755551234 00 0000 0000 07 5555 1234
Phone Numbers with Brackets 755551234 (00) 0000 0000 (07) 5555 1234
Fractions 10.5 # ??/?? 10ย  1/2



How to save time with Custom Cell Formats

1)ย ย ย ย ย  From time to time I create a reference sheet like a contacts list, an index or even just a list of items like the one below using the custom cell format @*.

Because the text in the first column is often different lengths it can be hard for the eye to follow across.ย  In these cases I like to use trailing dots to help the reader.

I wouldnโ€™t dream of manually entering the dots but since I can create a custom format itโ€™s worth it, plus I think it looks more elegant that using borders for this purpose as they can get a bit busy.

The custom cell format for trailing dots is @*. When you type in your text Excel will automatically enter the dots to fill to the end of the cell.

Tip: Youโ€™re not just limited to dots.ย  You can have ---- or **** or ____ or almost anything you want.ย  Just replace the dot in the custom format with the character of your choice.

2)ย ย ย ย ย  The other custom format I use regularly is prefixing my data with text.ย  For example, I keep a record of our invoices and instead of typing โ€˜INVโ€™ before each number I enter I use a custom cell format like this: โ€œINVโ€ 0000

Then when I type in 597 Excel converts it to INV 0597.

Tip: Replace INV with different text to suit your needs.ย  It might be PO for purchase order, or any other text you can think of.ย  Or make the text a suffix by changing the custom format to 0000 "INV".

Remember that even though the text appears to be INV 0597, for the purpose of formulas itโ€™s still just a number 597.

custom cell format credit card numbers

Formatting cells for credit card numbers

You might be thinking you can use a custom format of 0000 0000 0000 0000 for credit card numbers, but youโ€™ll find that it will only work for cards where the last number of the card is a zero! Try it out and see for yourself.

The workaround is to use a formula.ย  This requires entering the number in one cell, and then in another cell you need to enter the following formula (assuming our credit card number is in cell A1):

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

Note: Iโ€™ve added spaces in the formula for clarity.

Some explanation:

  • The LEFT, MID and RIGHT returns text from a specified position in a cell.
  • The ampersands โ€˜&โ€™ join text together
  • The โ€œ โ€œ adds a space between each group of text

[UPDATE] - You can also use this custom number format for credit cards and long phone numbers:

[<=99999999999]##########;#### #### #### ####

Thanks to MF for sharing that tip.

Enter your email address below to download the sample workbook.



By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the quick reference table below and print it off or save it for future use. Note: This is a .xlsx file. Please ensure your browser doesn't change the file extension on download.

Excel custom cell format examples

More Excel Posts

linear regression

Excel Linear Regression

Excel linear regression is easy with the built-in tools. Use charts to plot linear regression or use the Data Analysis Toolpak.
speed up slow excel files

How to Improve Excel Performance

How to improve Excel performance and the various causes of slow Excel files so you can speed up Excel and avoid problems in future.
Securely Share Excel Files

Securely Share Excel Files

Securely share Excel files stored locally, on OneDrive or SharePoint. Prevent editing or downloading, specify who can open and edit the file.
excel check boxes

Interactive Excel Check Boxes

Excel check boxes are interactive elements you can link to formulas, charts, conditional formatting and more.
tips for working in multiple excel files

Hacks for Working in Multiple Excel Files

Awesome tips for navigating, arranging and working in multiple Excel files. Guaranteed to streamline your workflow and increase productivity.
chatgpt for excel

ChatGPT for Excel

Using ChatGPT for Excel can be hit and miss. Learn the best uses for ChatGPT to make your Excel life easier and what to avoid using it for.
excel templates

Where to Find Free Excel Templates

Where to find free Excel templates and how to create your own Excel templates. Using templates saves time and effort.
Easily Remove Password Protection from Excel Files

Easily Remove Excel Password Protection

How to remove Excel password protection when youโ€™ve forgotten the password. Works for sheets, workbooks and read only files.
Import data from a picture to Excel

Import Data from a Picture to Excel

Import data from a picture to Excel. Works with pictures from a file or the clipboard and loads it to the spreadsheet.
excel online

5 Excel Online Features Better than Desktop

5 Excel Online Features Better than Desktop including searchable data validation, track changes, single line ribbon and more.


Category: Excel
Previous Post:index and match formulasINDEX MATCH Functions in Excel
Next Post:Save Time With Excel Tablesexcel tables

Reader Interactions

Comments

  1. Chris

    September 23, 2021 at 12:46 am

    I use the same customer format each day. ‘###############’, Is there a way to add this custom format in excel so that it is in the custom drop down list permanently? Not just for the workbook I used the day before, but for every workbook I open, so I don’t have to create it for each and every workbook I use?

    Reply
    • Mynda Treacy

      September 23, 2021 at 9:04 am

      Hi Chris,

      You can save the format to your default Excel workbook.

      Mynda

      Reply
  2. Md.Touhid Hasan

    January 3, 2020 at 4:53 am

    Do I use the SUMIFS way on data file?

    Reply
    • Philip Treacy

      January 3, 2020 at 8:20 am

      Hi,

      Sorry I don’t understand your question, can you please rephrase it and provide as much information as possible.

      Regards

      Phil

      Reply
  3. bpraj

    December 30, 2019 at 2:50 pm

    How to made text between bracket in one cell
    ex: in one cell –> ( tr )
    bracket on left and right, and text on center, all in one cell

    Reply
    • Mynda Treacy

      December 30, 2019 at 2:53 pm

      Custom cell format:

      (@)

      The @ symbol represents text.

      Mynda

      Reply
  4. Fekadu Tefera

    August 7, 2018 at 9:10 pm

    I like to work on EXCEL efficiently.

    Reply
  5. Benjamin Pam

    June 20, 2018 at 2:55 pm

    I find it very helpful

    Reply
  6. Nicole Mattson

    June 18, 2018 at 8:01 am

    I want to know how to calculate my times for work. This is hard to understand.

    Reply
    • Mynda Treacy

      June 18, 2018 at 8:07 am

      Hi Nicole,

      This tutorial explains how dates and times work in Excel. If you’re still stuck after that, please post your question in our forum where we can help you further.

      Mynda

      Reply
  7. Chris Parkinson

    December 8, 2016 at 8:46 pm

    you shouldnt be encouraging people to capture credit card numbers in Excel documents as that causes big problems with security and PCI compliance.

    Reply
    • Mynda Treacy

      December 8, 2016 at 9:36 pm

      Hi Chris,

      I’m not sure I said anywhere that people should capture credit card numbers in Excel. I just told them how to format them if they wish to.

      Some people use Excel to record their own credit card numbers, which doens’t breach PCI compliance. Now I’m not saying I recommend they do that either, but if they want to do so that’s up to them, and at their own risk.

      Mynda

      Reply
  8. albert

    December 8, 2016 at 11:52 am

    I have difficulty with the custome format…I cannot make @*. I only can find @. pls help.

    Reply
    • Mynda Treacy

      December 8, 2016 at 1:17 pm

      Hi Albert,

      The format is entered as a custom number format. You won’t find it in the list.

      There are 3 characters in the format like this: @*.

      You choose ‘Custom’ from the Category list and then enter the format in the ‘Type’ field.

      Let me know if you still have trouble.

      Mynda

      Reply
  9. Yakub Ansari

    November 11, 2016 at 5:21 pm

    I need a help for converting numbers to text. I have a data in outlook with 19 to 20 digit along with some other text fields in vertical format. While I copy paste this data in excel these 19 digits are pasted as numbers and they are changed to 000 at the end or I get a number like 65962656+000. For this I have to again copy the data from source and put an apostrophe before pasting the 19 digits to show as text.

    Can we have a solution here to save my extra effort of putting an apostrophe?

    Reply
    • Catalin Bombea

      November 12, 2016 at 1:32 am

      Have you tried to format the destination range as Text, then paste source data as values? This should stop excel from automatically convert numbers. When excel converts a number, only 15 digits are accepted, the rest will be replaced with 0, changing the destination format to text should be enough.

      Reply
  10. Joe Hendriks

    October 9, 2016 at 2:30 am

    I would like to create an Excel spreadsheet to calculate my nightshift working hours subtracting my unpaid break in minutes. running horizontally. Thanks.

    Reply
    • Catalin Bombea

      October 11, 2016 at 2:57 am

      Hi Joe,
      Can you upload a sample file with your data on our Forum?
      It will be easier to help you when we know your data structure, most of the times there will be differences between when you mean and what we understand from your explanations.
      Thanks for your understanding
      Catalin

      Reply
  11. Michael Hucke

    July 12, 2016 at 1:30 am

    Somewhere I saw how to build this custom format โ–ฒ_(* #,##0.00_);[Red]โ–ผ_(* (#,##0.00);_(* “-“??_);_(@_) but now I cannot find where I saw this. Unfortunaetly I did not write down how I constructed the custom format. Can you direct me to where I can find this. It is not part of the custom format blog posting. Thanks

    Reply
    • Mynda Treacy

      July 12, 2016 at 9:49 am

      Hi Michael,

      I’m not sure where you saw that format. It might have been in my Excel Dashboard course. I wrote a comprehensive guide to Excel Custom Number Formats.

      Mynda

      Reply
  12. Mike

    October 17, 2015 at 5:10 am

    Great site, I have a custom date format formula I need help with. I need to print timesheets monthly for weekdays only, and have formatted the cell to read the date in the ‘ddd’ format (Mon, Tue, Wed, etc.). I have tried this formula to skip the weekend dates =IF(‘1’!A1=”Fri”, ‘1’!A1+3, ‘1’!A1+1). I can’t get it to read the ddd format as “Fri”, it just reads the statement as false and adds 1 day to the date in cell A1. Any idea on how to write this formula without having to create a VLOOKUP?

    Reply
    • Catalin Bombea

      October 19, 2015 at 1:47 pm

      Hi Mike,
      The date in A1 is a number, as you already know. There will be a mismatch if you try to compare a number to a text, both items should have the same data type. Because “Fri” is a text, you have to convert the number from A1 to a text. The easiest way is using the TEXT formula: =IF(TEXT(โ€™1โ€ฒ!A1,”ddd”)=โ€Friโ€, โ€™1โ€ฒ!A1+3, โ€™1โ€ฒ!A1+1)
      This way, you will have both terms with the same data type, and you will be able to compare them.
      Cheers,
      Catalin

      Reply
      • Mike

        October 19, 2015 at 10:13 pm

        Catalin, your suggestion worked. I knew there was something missing from my formula that was causing it to be misread. It hadn’t occurred to me to try the TEXT formula.
        You are the best!
        Mike

        Reply
        • Catalin Bombea

          October 20, 2015 at 12:38 am

          Hi Mike,
          it’s always a pleasure when we can help ๐Ÿ™‚
          Cheers,
          Catalin

          Reply
  13. Jeevan A Y

    October 7, 2015 at 5:46 pm

    Absolute cracker

    Reply
    • Mynda Treacy

      October 7, 2015 at 9:35 pm

      Thanks, Jeevan! ๐Ÿ™‚

      Reply
  14. David Rockwell

    May 19, 2015 at 9:34 am

    I am looking to add 5 hours to a CDT number in the format dd/mm/yyyy hh:00 to get a GMT value. Can you help?

    Reply
    • Catalin Bombea

      May 19, 2015 at 12:36 pm

      Hi David,
      The format is irrelevant, because any date/time represents a number, it can be displayed in many ways, but it’s still a number, days are integers, and hours are subunits. 1 day represents 1 integer, so 1 hour is 1/24. To add 5 hours, simply add 5/24 to your time:
      =A1+5/24
      Cheers,
      Catalin

      Reply
  15. Rene

    March 18, 2015 at 7:01 pm

    Hi there, I need some help with a formula please.

    I want to set up a time sheet with only time in, time out, hours worked subtracting 1 hour for lunch and then at the end of the month calculate the hours, but display as 30 min in stead of .50

    Should look like this
    Time in Time out Daily hours Monthly hours
    09:30:00 AM 03:00:00 PM 05:30:00 5.30
    09:30:00 AM 03:00:00 PM 05:30:00 11.00

    Looking forward to hearing from you soon,

    Reply
    • Catalin Bombea

      March 18, 2015 at 7:16 pm

      Hi Rene,
      Displaying time in decimal system style format is confusing, and may be wrongly interpreted… 5.5 is in decimal system, 5:30 is in sexagesimal system, but both mean the same thing: 5 hours and a half.
      If you still want that, you can format Monthly Hours as time format, with this custom format: [h].mm
      Catalin

      Reply
  16. Bjorn Andersson

    April 6, 2014 at 11:37 pm

    Great information on this page, thanks. Hi have a question I can’t find any answer to. You can use the custom cell format to convert digits/numbers to letters or words like:

    [=1]”yes”;[=2]”No”

    I wont to do the opposite! In the cell the operator makes his entry I want to check if the entry is “x” and in that case replace the “x” with a number. If the operator enters a number I want to leave that as it is. If possible I don’t want to use VBA! Any solution you can think of?

    Reply
    • Catalin Bombea

      April 7, 2014 at 2:58 am

      i’m afraid that’s not possible, the text operator (@) is limited. Only VBA can be used…
      Catalin

      Reply
  17. Alan

    April 2, 2014 at 1:54 pm

    I accidentally deleted a custom cell format that essentially makes a cell a blank rather than a “0” (last choice in the custom Type).
    How can I get that blank back?

    Reply
    • Mynda Treacy

      April 2, 2014 at 2:30 pm

      Hi Alan,

      Custom number formats are made up of 4 components:

      Positive values ; negative values ; zero values ; text

      Not all values need to be stipulated in a custom number format. e.g. you can just stipulate positive and negative values and zeroes will be treated as a positive value.

      If you want to hide zeroes then you need to make sure the third argument is empty e.g.:

      0.0;-0.0;;

      The above format will format positives and negatives but because the zero format is empty they will not be displayed on your worksheet.

      Let me know if you get stuck.

      Kind regards,

      Mynda.

      Reply
  18. Saurav Sarkar

    March 22, 2014 at 10:13 pm

    I have my sales data in Thousands , but I want to show it in millions in the summary report and also in the charts for monthly meeting, – is it possible without converting the original data into millions

    Reply
    • Catalin Bombea

      March 23, 2014 at 3:12 pm

      Hi Saurav,
      You can right click on the axis values, choose Format Axis, under Axis Options, select Display Units: – Millions (usually, the default in None)
      A value of 8000, for example, will be displayed as 0,008 Millions.
      Catalin

      Reply
  19. Sherry

    March 22, 2014 at 1:22 am

    This tip came a just the right time. I was struggling with custom formatting for something very similar just yesterday.

    Thank you for the tips. They really make our lives easier.

    Reply
    • Mynda Treacy

      March 22, 2014 at 7:23 am

      Great! Glad we could help, Sherry ๐Ÿ™‚

      Reply
  20. Frank Collins

    January 16, 2014 at 10:25 am

    Hi,
    How is the normal 10 digit mobile phone number formatted, as in 0400 600 800, please? (Group of four digits, space, group of three digits, space, group of three digits)

    Reply
    • Catalin Bombea

      January 16, 2014 at 12:01 pm

      Hi Frank,
      Right click the cell, Format Cell–>Custom, and in Type section, type: (0000) 000 000 with or without the brackets, as you like.
      Catalin

      Reply
      • Frank Collins

        January 16, 2014 at 12:08 pm

        Thanks Catalin,
        I’ve already tried that and it says “cannot use the number format typed”. I have Office 2010 (and hate it).

        Reply
        • Catalin Bombea

          January 16, 2014 at 12:33 pm

          I also use Office 2010, and it worked without problems. Use “” if you are typing other characters than the brackets or spaces. Try this: In Special formatting category, you might have a Phone Number preset.

          Reply
          • Frank Collins

            January 16, 2014 at 1:03 pm

            Thanks, it worked. The Excel help from MS is woeful.

  21. Mike

    December 3, 2013 at 11:55 am

    I would like to be able to enter a decimal number into a cell such as 25.5 and see the format converted to feet and inches as follows: 25′ – 6″

    I have found a custom format that goes as far as 25′ – 6/12″

    Is there some way to truncate the /12 from the above format?

    Thank you,
    Mike

    Reply
    • Catalin Bombea

      December 3, 2013 at 9:45 pm

      Hi Mike,
      Unfortunately, the custom formats are limited, there is no way to do that…
      All you can do is to enter decimal values in a column, and use formulas to format as you like in next column.
      Cheers,
      Catalin

      Reply
  22. mano

    October 7, 2013 at 3:59 pm

    hi mynda ; perfeact .Cheers

    Reply
    • Mynda Treacy

      October 7, 2013 at 10:26 pm

      Thanks, Mano ๐Ÿ™‚

      Reply
  23. Dan W

    July 25, 2013 at 12:29 am

    I am using the trailing dots in 2 excel sheets. The first one worked flawlessly using the @*. method. The 2nd sheet is not being cooperative. It is working for some of the cells, but not all of them. The cells all have the same basic content – numbers or numbers and letters – I don’t see any visible reason this would not work, and when I click on the cells not working the formula bar shows the text is in the cell, and clicking on cell properties indicates the formatting is applied.

    Reply
    • Mynda Treacy

      July 25, 2013 at 9:22 am

      Hi Dan,

      Sounds strange. Are you able to send me your Excel file via the help desk so I can see what you’re working with?

      Cheers,

      Mynda.

      Reply
  24. Sharon

    May 15, 2013 at 12:46 am

    I need to set up a spread sheet to show 72 hours of an advanced date of meetings. Example: If there is a meeting set for Monday at 8:00 a.m. I would need all documents 72 hours prior to that day and time. How would I calculate this?

    Reply
    • Mynda Treacy

      May 15, 2013 at 10:01 am

      Hi Sharon,

      In your meeting time cell (say A2) enter the date and time as follows:

      20/05/2013 8:00 AM or if you use mm/dd/yyyy date format enter it as 05/20/2013 8:00 AM

      Then in cell B2 enter this formula:

      =A2-3

      3 is 3 days or 72 hours.

      Kind regards,

      Mynda.

      Reply
  25. Cathy Appleton

    May 7, 2013 at 11:16 pm

    Hi okay, i’ve searched around and can not find anything on this scenario. I have 12 digits, the first two need to be surrounded by parentheses and are either a space w/ one letter, two letters, or two numbers; the rest are all numbers w/ dashes between some of the numbers. I would like a formula where it will allow the first two digits to be either the letter or number. I enter all 12 digits in the cell and the formula produces either…
    (AL) 05-10-451-013
    (88) 20-12-376-101
    (K) 25-14-152-078
    a formula for the cell would be extremely appreciated, if even possible. Cathy

    Reply
    • Mynda Treacy

      May 8, 2013 at 10:05 am

      Hi Cathy,

      ="("&LEFT(A1,SEARCH(" ",A1)-1)&") "&RIGHT(A1,LEN(A1)-SEARCH(" ",A1))

      Where your value is in A1

      Kind regards,

      Mynda.

      Reply
  26. Shazif

    April 2, 2013 at 7:55 pm

    Its really a usefull information. THANKS a ton for it.

    You guys are really doing a tremendous job to promote advanced excel. Many people use this incredible thing just for entering the data and dont know how to use any of the remarkable formulae.

    Keep the good work going. Thanks…

    Reply
    • Carlo Estopia

      April 3, 2013 at 12:20 am

      Hi Shazif,

      On behalf of Mynda,
      You’re welcome!!!

      Carlo

      Reply
      • Aparna Majumdar

        April 23, 2013 at 1:43 am

        1. The custom cell format for trailing dots is @*. When you type in your text Excel will automatically enter the dots to fill to the end of the cell
        not very clear, I typed – ansbgv in left cell then gap few cells on right then typed gjkhuy. I wanted to link between ansbgv & gjkhuy with trailing dot – how?

        2. The other custom format I use regularly is prefixing my data with text. For example, I keep a record of our invoices and instead of typing โ€˜INVโ€™ before each number I enter I use a custom cell format like this: โ€œINVโ€ 0000
        Then when I type in 597 Excel converts it to INV 0597 =
        Which tools need to be used to convert?

        Thank you

        Reply
        • Carlo Estopia

          April 23, 2013 at 6:13 pm

          Hi Aparna,

          Regarding Solution for number 1, I am sorry but I don’t think it was meant for linking two words in a cell. That is why
          it is called , as you said, ‘trailing dots’.

          Regarding number two I don’t understand what you want here. You stated perfectly your case and it seems you have your
          answer already. So what do you mean by “tools to convert”?

          Cheers,

          CarloE

          Reply
  27. Markus

    March 30, 2013 at 5:14 am

    I want to be able to type in numbers from 1 through 59 and the cell automatically formats to read 1 min. through 59 min. i do this with _(#_*”min.” as a custom format.

    the problem arises when I type in 60 I want it to display as 1 hr.

    I don’t want formulas in the cells.

    any calculations need to be invisible or possibly handled as a VBA for the workbook.

    I want to be able to type in 65 and have it display as 1 hr. & 5 min.

    please send answers to markhamilton5@att.net

    Reply
    • Carlo Estopia

      April 1, 2013 at 10:43 pm

      Markus,

      As much as we wanted to help you,
      VBA is outside the scope of services/support
      we are currently providing.

      Please do come back when we will have VBA services
      soon.

      Cheers,

      CarloE

      Reply
  28. Lenin

    March 27, 2013 at 3:30 pm

    thanks for your tips

    Reply
    • Carlo Estopia

      March 27, 2013 at 4:23 pm

      Hi Lenin,

      You are very welcome,
      On behalf of Mynda.

      Cheers,

      CarloE

      Reply
  29. Gerry Smith

    March 12, 2013 at 1:29 pm

    Regarding the sixteen digit credit card number, it is not the fact that the last number has to be zero, it is that Excel uses 15 digit numbering.
    Try it with a twenty digit number, the last five digits will translate to zeros.

    Reply
    • Mynda Treacy

      March 12, 2013 at 1:42 pm

      Cheers, Gerry. This is why when the last digit is in fact a zero it ‘appears’ to work.

      Reply
  30. Ali

    January 1, 2013 at 9:12 pm

    It’s Fantastic, Thanks a lot.

    Reply
    • Mynda Treacy

      January 1, 2013 at 9:59 pm

      Cheers, Ali ๐Ÿ™‚

      Reply
  31. Solomon

    November 14, 2012 at 7:44 pm

    Hi ,
    thanks a lot for your excel formating content. i have a phone field some time it will have 10 digit value (e.g.7877988334) some time it may have phone field with the country code ( e.g. (+91) 7877988334 ). how to create the format to support both of them.

    waiting for your reply,

    with regards
    Solomon

    Reply
    • Mynda Treacy

      November 15, 2012 at 12:02 pm

      Hi Solomon,

      You can’t have one custom format that handles different scenarios.

      Kind regards,

      Mynda.

      Reply
    • MF

      February 9, 2014 at 1:48 am

      Inspired by the last comment, try this in the custom format:
      [<=99999999999]##########;(+##) ##########
      If your input is 12 digits, the latter format applies.

      Reply
      • Mynda Treacy

        February 10, 2014 at 9:59 am

        Thanks, MF. You’re a genius ๐Ÿ™‚

        I will have to share this magic with our members.

        Likewise you can modify it for credit card numbers:

        [<=99999999999]##########;#### #### #### #### Cheers, Mynda.

        Reply
        • MF

          February 27, 2014 at 5:20 pm

          Thanks for your kind word!

          I just learn from all of you.

          Reply
  32. Raghu

    November 12, 2012 at 3:13 pm

    Awesome, as usual. I daily just wait for your article.
    Another way to enter the credit card number is to add – โ€˜ ( key on the left side of enter key). The number is stored in text, However this is on the best way.

    Reply
  33. Evelyn Mc Kay

    October 5, 2012 at 6:08 pm

    thanks

    Reply
    • Mynda Treacy

      October 6, 2012 at 7:21 am

      ๐Ÿ™‚ You’re welcome.

      Reply
  34. asghar

    July 16, 2012 at 3:34 pm

    Realy a greate tutorial, I find this tutorial very helpful,

    Reply
    • Mynda Treacy

      July 16, 2012 at 4:16 pm

      Cheers, Asghar ๐Ÿ™‚ Glad you liked it.

      Reply
  35. Jenny Labbett

    February 6, 2012 at 8:02 pm

    Some great tips I had not come across before!

    Reply
    • Mynda Treacy

      February 6, 2012 at 8:19 pm

      Thanks Jenny ๐Ÿ™‚

      Reply
  36. Melanie Davis

    January 8, 2012 at 3:03 am

    Thanks, it was just what I needed.

    Reply
    • Philip Treacy

      January 8, 2012 at 8:02 am

      You’re welcome Melanie. Glad we could help.

      Reply
  37. Sherree Jones

    June 9, 2011 at 10:13 am

    Great website guys ๐Ÿ™‚ This kind of help is worth its weight in gold! Well done to you, it should go really well! Sherree xo

    Reply

Trackbacks

  1. Microsoft Excel Cell Formatting Force Text to Wrap, Merge & Center says:
    January 7, 2011 at 3:56 pm

    […] you could use one of the custom cell formats in one of my previous […]

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

239 Excel Keyboard Shortcuts

Download Free PDF

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 ยท My Online Training Hub ยท All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.