## How to format numbers as text

A number that is formatted as text will be left-aligned instead of right-aligned, this makes it easier for you to spot numbers that have been incorrectly formatted as text values.

The first example shows how Excel shows a number formatted as a number. The number is aligned to the right whereas numbers formatted as text are aligned to the left in the cell.

#### Method 1

**Instructions:**

- Select a cell
- Type '
- Type the number
- Press Enter

This example is shown in the second row in the image above.

#### Method 2

**Instructions:**

- Select a cell
- Type ="
*number*" - Press Enter

The *number* is the number you want to be formatted as text. This example is shown in the third row in the image above.

#### Method 3

**Instructions:**

- Select a cell
- Type the number
- Press Enter
- Press CTRL + 1 to open the "Format Cells" dialog box.
- Make sure you are on tab "Number".
- Select Category: "Text".
- Press with left mouse button on OK button.

### Number incorrectly formatted as a text value

A green arrow appears in the upper left corner of the cell if Excel believes you have a number in your worksheet that was formatted incorrectly as text.

Press with left mouse button on the exclamation button and an options menu appears.

This lets you quickly deal with the problem:

Convert to Number | Changes the formatting to a number. Value is now right-aligned. |

Help on this Error | Opens a help guide inside Excel. |

Ignore Error | Removes the green arrow, everything else remains as it is. |

Edit in formula Bar | The prompt is moved to the formula bar where you can edit the cell value. |

Error Checking Options... | Opens the "Excel Options" dialog box, see picture below. This allows you to change how Excel behaves when it comes to error values. |

If you have multiple values checked as errors you can easily remove the green arrows by selecting the cells containing the error arrow. Then press with left mouse button on the "Exclamation mark" button and lastly press with left mouse button on "Ignore error".

Use the following formula to convert a column number to a column letter: =LEFT(ADDRESS(1, B3, 4), MATCH(B3, {1; 27; 703})) […]

The formula in column B returns a running count based on values in column C. Formula in cell B3: =IF(C3<>"",COUNTA($C$3:C3),"") […]

The Quick Access Toolbar is located at the very top of your Excel window, I highly recommend that you place your […]

In this article, I am going to show you two ways on how to find blank cells. Both techniques are […]

Excel has great built-in features. The following one lets you search an entire worksheet for formulas that return an error. […]

Selecting cell ranges in Excel can sometimes be a real pain scrolling forever it seems. There is a quick and easy […]

A non-contiguous list is a list with occasional blank cells and that makes it harder to select the entire cell […]

In this smaller example, column D (Category) has empty cells, shown in the picture above. If your column contains thousands of […]

This article explains how to substitute part of a formula across all cells in a worksheet. It is easier than […]

This article demonstrates how to select all blank cells in a given cell range and how to delete them. It […]

The picture above shows data in column B, some cells contain nothing, they are blank. I will now go through […]

What is a reference in Excel? Excel has an A1 reference style meaning columns are named letters A to XFD […]

Making your sheets easy to read is a fundamental approach of creating useful worksheets. Your message must be crystal clear, […]

Have you ever wondered how these lines got there on a worksheet? They show where pages will break, in other […]

If your cell text is taking to much space Excel allows you to rotate text in any angle. Here are […]

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form