convert text to number

[Triniti]

Hi,

I have imported numbers from web to excel and I tried to summerize it but
the sum turns zero. I tried to change the original format to numberbut it
doesn't work. The original format is _(* #,##0.00_);_(* (#,##0.00);_(*
"-"??_);_(@_). Also I removed the $. Below is the examle.

Can someone advise?

$1,100.00
$6,000.00
$5,000.00
$2,400.00
Sum 0


Thanks

 

[Dave Peterson]

Depending on how you imported the data, maybe you got extra characters in those
cells--and those extra characters could make them strings/text--not real
numbers.

Those Non-breaking HTML spaces (char(160)'s) are the culprit lots of times.

David McRitchie has a macro that can help clean this:
http://www.mvps.org/dmcritchie/excel/join.htm#trimall
(look for "Sub Trimall()")

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

 

[Pete_UK]

When you import data from the web you often get space characters, in
particular the non-breaking space character (with a code of 160). You can
get rid of it using Find & Replace, or you can introduce a formula in a
helper column to remove it - the formula might use SUBSTITUTE or LEFT or
RIGHT, depending on your data. With the formula method you will have to
apply some simple arithmetic to the result to convert it into a number (e.g.
*1 or +0).

Hope this helps.

Pete

 

[Triniti]

Thanks to all! It helped! I used the find and replace Alt+0160 and it worked

Appreciate your quick response.

 

[Pete_UK]

You're welcome - thanks for feeding back.

Pete

Thanks to all! It helped! I used the find and replace Alt+0160 and it
worked

Appreciate your quick response.