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

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

Take the Value for each

=Value(A1) on the worksheet

or

Val(myValue) in code

Then format as a numeric.

--

Regards,
Nigel
(E-Mail Removed)



"S Himmelrich" <(E-Mail Removed)> wrote in message
news:b7fe9c16-490d-400f-8465-(E-Mail Removed)...
> 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
>

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 <peter...@verizonXSPAM.net> 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 -

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

On Feb 1, 11:30*am, "Nigel" <nigel-...@nosupanetspam.com> wrote:
> Take the Value for each
>
> =Value(A1) * * on the worksheet
>
> or
>
> Val(myValue) in code
>
> Then format as a numeric.
>
> --
>
> Regards,
> Nigel
> nigelnos...@9sw.co.uk
>
> "S Himmelrich" <himmelr...@gmail.com> wrote in message
>
> news:b7fe9c16-490d-400f-8465-(E-Mail Removed)...
>
>
>
> > 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 -

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

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 <peter...@verizonXSPAM.net> 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

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

On Feb 1, 12:22*pm, Dave Peterson <peter...@verizonXSPAM.net> 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 <peter...@verizonXSPAM.net> 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 -