View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Dave Peterson Dave Peterson is offline
external usenet poster
 
Posts: 35,218
Default Coverting strings to numbers and trimming trailing zeros

And you did change the numberformat to General first, right?

If yes, then changing the dot to dot will make excel reevalate the stuff in each
cell. If they're really numbers, then excel will see them as numbers. And with
a general format, the trailing .00 won't show.

But it sounds like your values aren't really digits (and dots).

If you got the data from a web page, then you may have HTML non-breaking spaces
in your data.

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

And if you're new to macros, you may want to read David's intro:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


S Himmelrich wrote:

I'm reading your comment as replacing a "." with a ".", hence I don't
understand how that helps...I've done what you have mentioned, but I
have the original results.

On Feb 1, 11:27 am, Dave Peterson wrote:
Try formatting the range as General
Then edit|replace
what: . (decimal point)
with: .
replace all

Record a macro when you do it in code and you should be ok.





S Himmelrich wrote:

Basically, I've tried to format the cell in a macro and as it's a
string it's not reformatting so this is the case I'm trying to
resolve . . .


My original data looks like this:


151.00
1.00
2.25
16.00
16.20


I'm looking for a result like this""
151
1
2.25
16
16.2


--

Dave Peterson- Hide quoted text -

- Show quoted text -


--

Dave Peterson