CODEWITHSUNDEEP
exceltype-conversion
sds
sds

Reputation: 231

How to convert text to numbers?

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?

Upvotes: 23

Views: 70843

Answers (8)

user121391
user121391

Reputation: 617

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).

  |  A  |  B  |
--------------
1 | abc |     |
2^| 1   |     |
3^| 2   |     |
4^| 3   |     |
5^| 4   |     |
6^| 5,6 |     |
7 | 7.8 |     |
8^| 9   |     |
9 | xyz |     |
--------------

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).

Upvotes: 0

KathyM
KathyM

Reputation: 1

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)))

Upvotes: 0

user3418162
user3418162

Reputation: 1

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

    Sub Enter_Values()
   For Each xCell In Selection
    xCell.Value = CDec(xCell.Value)
 Next xCell 
 End Sub

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

Sub Enter_Values()
For Each xCell In Selection
If IsNumeric(xCell) = True Then
    xCell.Value = CDec(xCell.Value)
    Else
     End If
 Next xCell
End Sub

Upvotes: 0

John Y
John Y

Reputation: 14559

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+).

Upvotes: 33

Rob Sedgwick
Rob Sedgwick

Reputation: 4514

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.

Upvotes: 0

Jeff
Jeff

Reputation: 31

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.

Upvotes: 3

Chase
Chase

Reputation: 69221

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

  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.

Upvotes: 0

Daniel DiPaolo
Daniel DiPaolo

Reputation: 56418

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

Upvotes: 11

Related Questions