In part cases, number in a worksheet room actually formatted and also stored in cells as text, which can cause problems with calculations or create confusing sort orders. This concern sometimes wake up after you import or copy data from a database or other exterior data source.

You are watching: You cannot format an entire column at once.

Numbers that space formatted as text are left-aligned instead of right-aligned in the cell, and are often marked with one error indicator.

What carry out you desire to do?

Technique 1: convert text-formatted number by utilizing Error Checking

If you import data into Excel from an additional source, or if you type numbers right into cells the were previously formatted together text, you might see a little green triangle in the upper-left corner of the cell. This error indicator tells you that the number is stored together text, as presented in this example.

*

If this isn"t what girlfriend want, you can follow these steps to convert the number that is stored together text ago to a constant number.

On the worksheet, select any single cell or selection of cell that has an error indicator in the upper-left corner.

How to pick cells, ranges, rows, or columns

To select

Do this

A solitary cell

Click the cell, or press the arrow keys to move to the cell.

A variety of cells

Click the an initial cell in the range, and also then drag to the critical cell, or host down transition while you push the arrow keys to prolong the selection.

You can likewise select the an initial cell in the range, and then push F8 to expand the an option by making use of the arrowhead keys. Come stop prolonging the selection, push F8 again.

A large range of cells

Click the first cell in the range, and also then hold down change while you click the critical cell in the range. You can scroll to make the critical cell visible.

All cell on a worksheet

Click the Select All button.

To select the whole worksheet, girlfriend can likewise press Ctrl+A.

If the worksheet consists of data, Ctrl+A selects the present region. Pressing Ctrl+A a 2nd time selects the entire worksheet.

Nonadjacent cell or cell ranges

Select the an initial cell or selection of cells, and then organize down Ctrl when you choose the other cells or ranges.

You can additionally select the first cell or variety of cells, and then press Shift+F8 to add another nonadjacent cell or variety to the selection. Come stop adding cells or varieties to the selection, press Shift+F8 again.

You cannot cancel the choice of a cell or variety of cell in a nonadjacent choice without canceling the entire selection.

An whole row or column

Click the heat or column heading.

1. Row heading

2. Column heading

You can likewise select cells in a row or obelisk by selecting the very first cell and also then pressing Ctrl+Shift+Arrow vital (Right arrowhead or Left arrowhead for rows, Up arrowhead or Down arrowhead for columns).

If the row or column consists of data, Ctrl+Shift+Arrow crucial selects the row or pillar to the last offered cell. Pressing Ctrl+Shift+Arrow crucial a second time selects the whole row or column.

Adjacent rows or columns

Drag throughout the row or obelisk headings. Or select the very first row or column; then host down transition while you select the last heat or column.

Nonadjacent rows or columns

Click the tower or heat heading that the very first row or tower in her selection; then organize down Ctrl while girlfriend click the shaft or heat headings of various other rows or columns the you desire to include to the selection.

The an initial or critical cell in a heat or column

Select a cell in the heat or column, and also then push Ctrl+Arrow vital (Right arrow or Left arrowhead for rows, Up arrow or Down arrow for columns).

The very first or last cell on a worksheet or in a rebab.net Office Excel table

Press Ctrl+Home to choose the an initial cell ~ above the worksheet or in an Excel list.

Press Ctrl+End to choose the critical cell ~ above the worksheet or in an Excel perform that consists of data or formatting.

Cells come the last provided cell ~ above the worksheet (lower-right corner)

Select the an initial cell, and also then push Ctrl+Shift+End to extend the selection of cell to the last used cell on the worksheet (lower-right corner).

Cells come the beginning of the worksheet

Select the an initial cell, and then push Ctrl+Shift+Home to extend the selection of cells to the beginning of the worksheet.

More or fewer cells 보다 the energetic selection

Hold down shift while friend click the last cell the you want to encompass in the new selection. The rectangular selection between the energetic cell and also the cell that you click i do not care the new selection.

To cancel a an option of cells, click any cell top top the worksheet.

Next come the selected cabinet or variety of cells, click the error button that appears.

*

peak of web page

Technique 2: convert text-formatted numbers by making use of Paste Special

In this technique, you multiply each selected cabinet by 1 in bespeak to force the conversion native a text-formatted number come a continual number. Because you"re multiply the components of the cabinet by 1, the an outcome in the cell looks identical. However, Excel actually replaces the text-based components of the cell with a numerical equivalent.

Select a empty cell and verify the its number format is General.

How to verify the number format

On the Home tab, in the Number group, click the arrowhead next to the Number Format box, and also then click General.

*

In the cell, kind 1, and also then press ENTER.

Select the cell, and also then press Ctrl+C come copy the value to the Clipboard.

Select the cells or arrays of cells the contain the numbers stored as text that you want to convert.

How to select cells, ranges, rows, or columns

To select

Do this

A solitary cell

Click the cell, or push the arrowhead keys to move to the cell.

A selection of cells

Click the very first cell in the range, and also then traction to the last cell, or organize down transition while you press the arrow keys to prolong the selection.

You can additionally select the an initial cell in the range, and also then press F8 to prolong the choice by making use of the arrowhead keys. To stop prolonging the selection, push F8 again.

A large range that cells

Click the first cell in the range, and also then hold down change while friend click the critical cell in the range. You have the right to scroll to do the last cell visible.

All cells on a worksheet

Click the Select All button.

To select the whole worksheet, friend can also press Ctrl+A.

If the worksheet consists of data, Ctrl+A selects the existing region. Pushing Ctrl+A a second time selects the entire worksheet.

Nonadjacent cells or cabinet ranges

Select the first cell or selection of cells, and also then host down Ctrl while you select the various other cells or ranges.

You can also select the very first cell or variety of cells, and also then press Shift+F8 to add another nonadjacent cell or range to the selection. Come stop including cells or ranges to the selection, press Shift+F8 again.

You can not cancel the choice of a cabinet or range of cells in a nonadjacent selection without canceling the entire selection.

An whole row or column

Click the row or shaft heading.

1. Row heading

2. Obelisk heading

You can likewise select cells in a row or obelisk by selecting the first cell and also then pressing Ctrl+Shift+Arrow vital (Right arrow or Left arrowhead for rows, Up arrowhead or Down arrowhead for columns).

If the heat or column consists of data, Ctrl+Shift+Arrow an essential selects the heat or shaft to the last supplied cell. Pressing Ctrl+Shift+Arrow vital a second time selects the whole row or column.

Adjacent rows or columns

Drag throughout the heat or obelisk headings. Or select the very first row or column; then host down change while you pick the last row or column.

Nonadjacent rows or columns

Click the obelisk or heat heading that the an initial row or obelisk in your selection; then host down Ctrl while you click the column or row headings of various other rows or columns that you desire to include to the selection.

The very first or critical cell in a heat or column

Select a cabinet in the heat or column, and also then press Ctrl+Arrow vital (Right arrowhead or Left arrowhead for rows, Up arrow or Down arrow for columns).

The an initial or critical cell ~ above a worksheet or in a rebab.net Office Excel table

Press Ctrl+Home to pick the first cell on the worksheet or in one Excel list.

Press Ctrl+End to pick the last cell on the worksheet or in one Excel perform that consists of data or formatting.

Cells to the last supplied cell on the worksheet (lower-right corner)

Select the an initial cell, and then press Ctrl+Shift+End to prolong the choice of cells to the last provided cell top top the worksheet (lower-right corner).

Cells come the start of the worksheet

Select the an initial cell, and also then press Ctrl+Shift+Home to prolong the selection of cells to the beginning of the worksheet.

More or fewer cells than the energetic selection

Hold down transition while girlfriend click the critical cell the you desire to encompass in the new selection. The rectangular range between the energetic cell and the cell that you click becomes the new selection.

To release a choice of cells, click any cell on the worksheet.

On the Home tab, in the Clipboard group, click the arrow below Paste, and also then click Paste Special.

Under Operation, choose Multiply, and then click OK.

To delete the content of the cell the you typed in action 2 after all numbers have actually been convert successfully, select that cell, and also then push DELETE.

Some audit programs display an unfavorable values together text, through the an unfavorable sign () come the ideal of the value. To transform the message string to a value, you should use a formula to return all the personalities of the text string other than the rightmost personality (the negation sign), and then multiply the result by –1.

For example, if the value in cabinet A2 is "156–" the following formula converts the message to the worth –156.

Data

Formula

156-

=Left(A2,LEN(A2)-1)*-1

optimal of web page

Technique 3: use a number layout to text-formatted numbers

In some scenarios, girlfriend don"t have actually to transform numbers stored as text back to numbers, as defined earlier in this article. Instead, you have the right to just apply a number layout to attain the exact same result. Because that example, if you get in numbers in a workbook, and then style those numbers together text, you won"t view a green error indicator appear in the upper-left edge of the cell. In this case, girlfriend can apply number formatting.

Select the cells the contain the number that space stored together text.

How to pick cells, ranges, rows, or columns

To select

Do this

A solitary cell

Click the cell, or press the arrow keys to relocate to the cell.

A range of cells

Click the an initial cell in the range, and then traction to the last cell, or organize down change while you push the arrowhead keys to prolong the selection.

You can additionally select the very first cell in the range, and also then press F8 to extend the an option by making use of the arrowhead keys. To stop prolonging the selection, push F8 again.

A big range the cells

Click the first cell in the range, and then organize down shift while girlfriend click the critical cell in the range. You can scroll to make the last cell visible.

All cells on a worksheet

Click the Select All button.

To choose the whole worksheet, friend can additionally press Ctrl+A.

If the worksheet consists of data, Ctrl+A selects the present region. Pushing Ctrl+A a second time selects the whole worksheet.

Nonadjacent cell or cell ranges

Select the first cell or range of cells, and then host down Ctrl while you choose the other cells or ranges.

You can likewise select the an initial cell or variety of cells, and then push Shift+F8 to add another nonadjacent cabinet or range to the selection. Come stop including cells or ranges to the selection, push Shift+F8 again.

You cannot cancel the an option of a cell or selection of cell in a nonadjacent an option without canceling the whole selection.

An entire row or column

Click the heat or pillar heading.

1. Heat heading

2. Column heading

You can also select cell in a heat or shaft by selecting the very first cell and also then pressing Ctrl+Shift+Arrow crucial (Right arrowhead or Left arrowhead for rows, Up arrowhead or Down arrowhead for columns).

If the heat or column consists of data, Ctrl+Shift+Arrow key selects the heat or tower to the last offered cell. Pushing Ctrl+Shift+Arrow crucial a 2nd time selects the entire row or column.

Adjacent rows or columns

Drag across the row or tower headings. Or pick the first row or column; then hold down transition while you select the last heat or column.

Nonadjacent rows or columns

Click the obelisk or row heading the the an initial row or column in her selection; then host down Ctrl while you click the column or heat headings of other rows or columns that you desire to include to the selection.

The very first or last cell in a row or column

Select a cabinet in the row or column, and also then press Ctrl+Arrow crucial (Right arrow or Left arrow for rows, Up arrow or Down arrow for columns).

The an initial or last cell on a worksheet or in a rebab.net Office Excel table

Press Ctrl+Home to select the very first cell ~ above the worksheet or in one Excel list.

Press Ctrl+End to pick the critical cell on the worksheet or in one Excel list that consists of data or formatting.

Cells come the last used cell top top the worksheet (lower-right corner)

Select the an initial cell, and also then press Ctrl+Shift+End to extend the selection of cell to the last provided cell top top the worksheet (lower-right corner).

Cells to the start of the worksheet

Select the first cell, and then press Ctrl+Shift+Home to prolong the selection of cells to the beginning of the worksheet.

More or under cells 보다 the energetic selection

Hold down transition while friend click the last cell that you want to incorporate in the new selection. The rectangular variety between the energetic cell and also the cell the you click i do not care the new selection.

To release a selection of cells, click any cell top top the worksheet.

On the Home tab, in the Number group, click the Dialog crate Launcher beside Number.

*

peak of page

Turn turn off Error Checking

With error checking turned on in Excel, you see a little green triangle if you get in a number into a cabinet that has text formatting used to it. If friend don’t desire to watch these error indicators, you have the right to turn castle off.

Click the File tab.

Under Help, click Options.

In the Excel options dialog box, click the Formulas category.

See more: What Is The Average Height For A 13 Year Old ? When Do Boys Stop Growing

Under Error checking rules, clean the Numbers formatted as text or came before by an apostrophe inspect box.