Text

[MileHigh]

I downloaded a report into excel. One of the columns are numbers but
can't calculate them. How do I change it to a number?

I don't want to use (example.. A1*1)

 

[Peo Sjoblom]

Copy an empty cell, select the import and do edit>paste special and select
add
If that doesn't work then you have invisible characters in the import

 

[Max]

Another way to try is to wrap VALUE() on the column

For example, if the "numbers" are in col A
and you're using some function, say

In C2: =VLOOKUP(A2,MyTable,2,0)*B2

Use instead in C2: =VLOOKUP(VALUE(A2),MyTable,2,0)*B2

You could of course use a helper column, viz.:

In C2: =VALUE(A2)
with C2 copied down col C

Then frame your downstream calculations on col C instead of col A

 

[Peo Sjoblom]

I thought that would go under

"I don't want to use (example.. A1*1)."

because that is basically what value does..


--

Regards,

Peo Sjoblom

Another way to try is to wrap VALUE() on the column

For example, if the "numbers" are in col A
and you're using some function, say

In C2: =VLOOKUP(A2,MyTable,2,0)*B2

Use instead in C2: =VLOOKUP(VALUE(A2),MyTable,2,0)*B2

You could of course use a helper column, viz.:

In C2: =VALUE(A2)
with C2 copied down col C

Then frame your downstream calculations on col C instead of col A

 

[Max]

Hmm, guess you're right, Peo, thanks.
Maybe I had disregarded the subtleties involved,
as well as the "don't want" constraint by the OP.

 

[Peo Sjoblom]

OTOH maybe the OP meant that he/she didn't want to go and edit every single
cell and didn't even know
one could use formula and a help column and fix it in less than a minute?

 

[Max]

Yes, also another possibility!

That's why it may be alright to lightly "disregard"
the OPs' "constraints" in providing suggested ways
to their problems.

Anyway, we'll never know unless the OPs themselves revert <g>

 

[Bas van Heek]

Are you sur the cells containing numbers are defined as numbers (format cells->Numbers)? If they are defined as text you cannot calculate them..

Regards
Bas

 

[yahoo]

They are defined as text - how do i convert the mto numbers
I used Format -> cells -> number - but it did not work

 

[Max]

They are defined as text - how do i convert the mto numbers
I used Format -> cells -> number - but it did not work

Formatting does not change the underlying values

Try this:

Right-click on an *empty* cell > Copy
Right-click on the range/col of "text" numbers
Choose Paste special > Add > OK