Converting Text to Numbers

[Guest]

Hello,

I have come across numbers that are formatted as text that I would like to
use in an equation. I have used the format text option and changed them all
to numbers, but it did not apply. I have looked at the error in the
calculation and it shows the cell as ="6 "*1, with 6 being and example of the
text that I would like to use as a number.

I have tried copying the value 1 and multiplying the cell with paste special
without any success. It seems that works when it is just single quotes out
front.

I appreciate anyones thoughts on how I can convert these text numbers to
just numbers

Thanks!
-Bob

 

[Frank Kabel]

Hi
try using 'Edit - Replace' to remove the double apostrophes

 

[Guest]

Frank,

Thanks for your reply. The apostrophe is actually not visibly there in the
cell. It was only noticed once I debugged the formula. I do not completely
understand how that works, but when I search for the " in edit-replace it
comes up empty. (It does not find any.)

Since I have written this question, I have played around with =LEFT, =RIGHT
and =LEN. I have managed to remove off the portion that seems to be making
it text. Essentially, I need to remove one character off the end of the
value in order to get a "pure number" =LEFT(A1,LEN(A1)-1) This has work
well for 80% of the cells, but the other 20% has an * after the number that I
would also like to remove. An example being in cell B1 is 28* . I tried
using =IF((RIGHT(B1,2)="*",LEFT(B1,(LEN(B1)-2)),LEFT(B1,(LEN(B1)-1))). This
did not work. I debugged it by doing =RIGHT(B1,2) which returned *. But
much to my surprise =IF(RIGHT(B1,2)="*",1,2) gave me 2. Why is it returning
2 for that last formula? Is there a problem with using charater
identification with if statements?

Thanks for your insight!
-Bob

 

[Frank Kabel]

Hi
try
=--SUBSTITUTE(A1,"*","")

--
Regards
Frank Kabel
Frankfurt, Germany

Frank,

Thanks for your reply. The apostrophe is actually not visibly there in the
cell. It was only noticed once I debugged the formula. I do not completely
understand how that works, but when I search for the " in edit-replace it
comes up empty. (It does not find any.)

Since I have written this question, I have played around with =LEFT, =RIGHT
and =LEN. I have managed to remove off the portion that seems to be making
it text. Essentially, I need to remove one character off the end of the
value in order to get a "pure number" =LEFT(A1,LEN(A1)-1) This has work
well for 80% of the cells, but the other 20% has an * after the number that I
would also like to remove. An example being in cell B1 is 28* . I tried
using

=IF((RIGHT(B1,2)="*",LEFT(B1,(LEN(B1)-2)),LEFT(B1,(LEN(B1)-1))). This