Wednesday, February 6, 2019

Format numbers as text in Excel for Mac

Format numbers as text in Excel for Mac

If you want Excel to treat certain types of numbers as text, you can use the text format instead of a number format. For example, if you are using credit card numbers, or other number codes that contain 16 digits or more, you must use a text format. That's because Excel has a maximum of 15 digits of precision and will round any numbers that follow the 15th digit down to zero, which probably isn't what you want to happen.

It's easy to tell at a glance if a number is formatted as text, because it will be left-aligned instead of right-aligned in the cell.

  1. Select the cell or range of cells that contains the numbers that you want to format as text.

    Tip: To cancel a selection of cells, click any cell on the worksheet.

    Tip: You can also select empty cells, and then enter numbers after you format the cells as text. Those numbers will be formatted as text.

  2. Click the Home tab.

  3. Click the arrow in the Number Format box, and then click Text.

    Format numbers as text

Tips

  • To use decimal places in numbers that are stored as text, you may need to include the decimal points when you type the numbers.

  • When you enter a number that begins with a zero—for example, a product code—Excel deletes the zero by default. If this is not what you want, you can create a custom number format that forces Excel to retain the leading zero. For example, if you're typing or pasting ten-digit product codes in a worksheet, Excel changes numbers like 0784367998 to 784367998. In this case, you could create a custom number format consisting of the code 0000000000, which forces Excel to display all ten digits of the product code, including the leading zero.

  • Occasionally, numbers might be formatted and stored in cells as text, which later can cause problems with calculations or produce confusing sort orders. This sometimes happens when you import or copy numbers from a database or other data source. In this scenario, you must convert the numbers stored as text back to numbers.

  • You can also use the TEXT function to convert a number to text in a specific number format.

No comments:

Post a Comment