Coverting strings to numbers and trimming trailing zeros

[S Himmelrich]

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]

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.

 

[Nigel]

Take the Value for each

=Value(A1) on the worksheet

or

Val(myValue) in code

Then format as a numeric.

 

[S Himmelrich]

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.

 

[S Himmelrich]

tried with and without code and this doesn't work...

 

[Ron Rosenfeld]

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

Although you did not mention it, I assume you want your values to be numbers
and right justified.

With your numbers starting in A1, something like this might work:

======================
Option Explicit
Sub cellformat()
Dim c As Range
Set c = Range("a1").CurrentRegion
c.NumberFormat = "General"
c.Value = c.Value
End Sub
=======================


--ron

 

[Dave Peterson]

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]

Talk about tricks of the trade...thank you much.

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








--

Dave Peterson- Hide quoted text -

- Show quoted text -