Coverting strings to numbers and trimming trailing zeros
 

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

Coverting strings to numbers and trimming trailing zeros
 

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

Coverting strings to numbers and trimming trailing zeros
 

Take the Value for each

=Value(A1) on the worksheet

or

Val(myValue) in code

Then format as a numeric.

--

Regards,
Nigel




"S Himmelrich" wrote in message
...
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

Coverting strings to numbers and trimming trailing zeros
 

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 -

Coverting strings to numbers and trimming trailing zeros
 

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

On Feb 1, 11:30*am, "Nigel" wrote:
Take the Value for each

=Value(A1) * * on the worksheet

or

Val(myValue) in code

Then format as a numeric.

--

Regards,
Nigel


"S Himmelrich" wrote in message

...



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- Hide quoted text -

- Show quoted text -

Coverting strings to numbers and trimming trailing zeros
 

On Fri, 1 Feb 2008 08:14:42 -0800 (PST), 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

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

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

Coverting strings to numbers and trimming trailing zeros
 

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

On Feb 1, 12:22*pm, Dave Peterson wrote:
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- Hide quoted text -

- Show quoted text -