Excel For Mac Convert Text Cell To Number Cell

It’s common to find numbers stored as text in Excel. This leads to incorrect calculations when you use these cells in Excel functions such as SUM and AVERAGE (as these functions ignore cells that have text values in it). In such cases, you need to convert cells that contain numbers as text back to numbers.

1. Excel For Mac Convert Text Cell To Number Cell Reference
2. Convert Text Cell To Date In Excel

If you have many cells that you want to convert from text into number format, this solution will likely be a better option for you. In an empty space in your Excel spreadsheet, add zero (i.e., 0) to one of the cells that contains data in text format (see below for an example where a 0 is added to cell C6). Microsoft Excel is great at working with both numbers and text Saving Time with Text Operations in Excel Saving Time with Text Operations in Excel Excel can do magic with numbers and it can handle characters equally well. This manual demonstrates how to analyze, convert, replace, and edit text within spreadsheets.

Now before we move forward, let’s first look at a few reasons why you may end up with a workbook that has numbers stored as text.

1. Using ‘ (apostrophe) before a number.
   • A lot of people enter apostrophe before a number to make it text. Sometimes, it’s also the case when you download data from a database. While this makes the numbers show up without the apostrophe, it impacts the cell by forcing it to treat the numbers as text.
2. Getting numbers as a result of a formula (such as LEFT, RIGHT, or MID)
   • If you extract the numerical part of a text string (or even a part of a number) using the TEXT functions, the result is a number in the text format.

Now, let’s see how to tackle such cases.

Convert Text to Numbers in Excel

In this tutorial, you’ll learn how to convert text to numbers in Excel.

The method you need to use depends on how the number has been converted into text. Here are the ones that are covered in this tutorial.

• Using the ‘Convert to Number’ option.
• Change the format from Text to General/Number.
• Using Paste Special.
• Using Text to Column.
• Using a Combination of VALUE, TRIM, and CLEAN function.

Convert Text to Numbers Using ‘Convert to Number’ Option

When an apostrophe is added to a number, it changes the number format to text format. In such cases, you’ll notice that there is a green triangle at the top left part of the cell.

In this case, you can easily convert numbers to text by following these steps:

• Select all the cells that you want to convert from text to numbers.
• Click on the yellow diamond shape icon that appears at the top right. From the menu that appears, select ‘Convert to Number’ option.

This would instantly convert all the numbers stored as text back to numbers. You would notice that the numbers get aligned to the right after the conversion (while these were aligned to the left when stored as text).

Convert Text to Numbers by Changing Cell Format

When the numbers are formatted as text, you can easily convert it back to numbers by changing the format of the cells.

Here are the steps:

• Select all the cells that you want to convert from text to numbers.
• Go to Home –> Number. In the Number Format drop-down, select General.

This would instantly change the format of the selected cells to General and the numbers would get aligned to the right. If you want, you can select any of the other formats (such as Number, Currency, Accounting) which will also lead to the value in cells being considered as numbers.

Convert Text to Numbers Using Paste Special Option

To convert text to numbers using Paste Special option:

• Enter 1 in any empty cell in the worksheet. Make sure it is formatted as a number (i.e., aligned to the right of the cell).
• Copy the cell that contains 1.
• Select the cells that you want to convert from text to numbers.
• Right-click and select Paste Special.
• In the Paste Special dialog box, select Multiply within the Operation category.
• Click OK.

Convert Text to Numbers Using Text to Column

This method is suitable in cases where you have the data in a single column.

Here are the steps:

• Select all the cells that you want to convert from text to numbers.
• Go to Data –> Data Tools –> Text to Columns.
• In the Text to Column Wizard:
  • In Step 1: Select Delimited and click on Next.
  • In Step 2: Select Tab as the delimiter and click on Next.
  • In Step 3: In Column data format, make sure General is selected. You can also specify the destination where you want the result. If you don’t specify anything, it will replace the original data set.

While you may still find the resulting cells to be in the text format, and the numbers still aligned to the left, now it would work in functions such as SUM and AVERAGE.

Convert Text to Numbers Using the VALUE Function

You can use a combination of VALUE, TRIM and CLEAN function to convert text to numbers.

• VALUE function converts any text that represents a number back to a number.
• TRIM function removes any leading or trailing spaces.
• CLEAN function removes extra spaces and non-printing characters that might sneak in if you import the data or download from a database.

Suppose you want convert cell A1 from text to numbers, here is the formula:

=VALUE(TRIM(CLEAN(A1)))

If you want to apply this to other cells as well, you can copy and use the formula.

Finally, you can convert the formula to value using paste special.

You May Also Like the Following Excel Tutorials:

• Multiply in Excel Using Paste Special.
• Convert Formula to Values Using Paste Special.
• Excel Custom Number Formatting.

Session expired

Please log in again. The login page will open in a new tab. After logging in you can close it and return to this page.

Active1 year, 2 months ago

I have an Excel report which contains several columns of text and several columns of numbers which are stored as text.

Is there an easy way to convert those numbers that are stored as text to numbers, without affecting the actual text data?

Gaffi

3,51766 gold badges3939 silver badges7070 bronze badges

sdssds

8 Answers

I find that the easiest and quickest way to convert 'numbers stored as text' into numeric numbers is

1. Select any blank cell
2. Copy that cell (Ctrl+C)
3. Select the range which contains the data you want to convert (it's OK if the range includes nonnumeric data as well)
4. Use 'Paste Special' with operation 'add' (I guess 'subtract' would work too)

Besides being quick, this has the advantage of converting in-place.

'Paste Special' is in the Edit menu of 'classic' versions of Excel (2003 and earlier), or in the Clipboard section of the Home tab of 'ribbon' versions of Excel (2007+).

John YJohn Y

11k11 gold badge3636 silver badges6060 bronze badges

Use the VALUE function if you just want that text as a number in a different cell.

Daniel DiPaoloDaniel DiPaolo

45.5k1313 gold badges106106 silver badges110110 bronze badges

Another workaround is to add zero to force a type conversion: If the cell A1 contains '5', yet ISNUMBER(A1) returns FALSE, ISNUMBER(A1+0) will return TRUE if A1 can be case into a number.

Excel For Mac Convert Text Cell To Number Cell Reference

JeffJeff

I'm not sure I understand your dilemma, but there are at least two solutions to your immediate question:

Convert Text Cell To Date In Excel

1. Format the cells that need to be numbers as 'number' as opposed to text. You can highlight the cells you want --> right click --> Format as number. You can set the number of decimal and specific formatting from there.

2. Create a new column that multiplies your text/number column times 1 and set the format to this new column as number.

ChaseChase

52.3k1313 gold badges123123 silver badges155155 bronze badges

Personally if there are just a few numbers I just press F2 on each cell and press enter, without any changes. That makes Excel think you have edited the cell and turned it into a number.

Rob SedgwickRob Sedgwick

2,78322 gold badges2626 silver badges5757 bronze badges

Cannot get these solutions to work properly with a mixed column of alpha and numeric where the numerics are text copied from a web page. A contributor (D. Mabutt) on About.com posted this VBA code

That code will throw an error on the alpha cells, so this should be added:

user3418162user3418162

Here is the solution I found for the situation I'm in.

I run a report weekly that requires copying and pasting approximately 7000 rows of data exported from a system. Recently some changes were made so that the numbers were coming out stored as text. The data includes a selection of actual text entries and the rest numbers stored as text. This data gets copied and pasted into a master template which then feeds other sheets/reports, but I needed the numbers to be stored as numbers. Due to the volume of the data and the fact that I'm not the only one performing this task (and some of the others are not Excel savvy) I needed to try and automate this process. I tried the =A1*1 formula and it worked for the numbers but the text I got a #VALUE! error. After much fiddling, I came up with the formula below and it works perfectly!

=IF(AND(A1='Yes'),'Yes',IF(AND(A1='Not applicable'),'Not applicable',IF(AND(A1='No'),'No',A1*1)))

La-comadreja

4,62188 gold badges2727 silver badges5454 bronze badges

KathyMKathyM

The other answers didn't help me, but I found out that at least in Excel 2010 (i guess it would be similar in other versions), you can use the little green triangle in the upper left of the affected cells after you've selected them.

What it doesn't tell you however, is that for it to work with multiple cells, you have to start your selection with a cell that contains a green triangle. The last cell may be any cell (the last cell can be anything). If you start with an non-green cell, the yellow exclamation mark will never appear.

I've made a simple table in an empty worksheet where every cell was converted to text via Format >Format Cell. I then entered the values below (I've marked the rows that contain green triangles with ^ in the diagram).

Now, you have to start your selection in A2 or A8 and drag the selection to the other cells for it to work. Starting at A9 or A1 does not work. Starting in between would work, but this way you cannot select all cells in one go.

Also the recognition of cells might depend on your locale - for countries that use points instead of commas, your results may vary (cell A6 vs. A7).

user121391user121391

Not the answer you're looking for? Browse other questions tagged exceltype-conversion or ask your own question.