Text

  • Thread starter Thread starter MileHigh
  • Start date Start date
M

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

Max said:
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
Click to expand...
 
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.
 
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?
 
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>
 
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
 
They are defined as text - how do i convert the mto numbers
I used Format -> cells -> number - but it did not work
 
yahoo said:
They are defined as text - how do i convert the mto numbers
I used Format -> cells -> number - but it did not work
Click to expand...

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
 
Back
Top