cell format: numbers won't be numbers

[Craig Fletcher]

I have some data that I copied out from a webpage (a credit card summary)
which I have since added several other columns around it (i.e., I would much
rather not have to paste it again) and the numbers won't act like numbers.
I've tried copying them out, and doing "paste special" into a new worksheet
with all of the different "paste special" options, and I have also tried all
of the formatting options (currency, numbers, etc.) and still they will not
be treated as numbers by Excel.

Is there anything else I can try?

Thanks,
Craig

[kassie]

Insert a 1 in an unused cell. Press <Ctrl><C>. Now select the range where
your numbers are not treated as numbers, then right click, select Paste
Special, and tick the Multiply option.

"Craig Fletcher" wrote:

> I have some data that I copied out from a webpage (a credit card summary)
> which I have since added several other columns around it (i.e., I would much
> rather not have to paste it again) and the numbers won't act like numbers.
> I've tried copying them out, and doing "paste special" into a new worksheet
> with all of the different "paste special" options, and I have also tried all
> of the formatting options (currency, numbers, etc.) and still they will not
> be treated as numbers by Excel.
>
> Is there anything else I can try?
>
> Thanks,
> Craig
>
>
>

[Craig Fletcher]

Thank you, that was a crafty idea, but alas, it did not work. <sigh> any
other ideas?

"kassie" <[email protected]> wrote in message
news:[email protected]...
> Insert a 1 in an unused cell. Press <Ctrl><C>. Now select the range
> where
> your numbers are not treated as numbers, then right click, select Paste
> Special, and tick the Multiply option.
>
> "Craig Fletcher" wrote:
>
>> I have some data that I copied out from a webpage (a credit card summary)
>> which I have since added several other columns around it (i.e., I would
>> much
>> rather not have to paste it again) and the numbers won't act like
>> numbers.
>> I've tried copying them out, and doing "paste special" into a new
>> worksheet
>> with all of the different "paste special" options, and I have also tried
>> all
>> of the formatting options (currency, numbers, etc.) and still they will
>> not
>> be treated as numbers by Excel.
>>
>> Is there anything else I can try?
>>
>> Thanks,
>> Craig
>>
>>
>>

[Gord Dibben]

Craig

The numbers are probably copied in as text.

Re-formatting alone will not change them.

Try this method...................

Format all cells to General.

Copy an empty cell and selet the "numbers"

Paste Special>Add>OK>Esc

That should change the "numbers" into real numerics.

If no joy, there could be spaces before or after the numbers.

These could be non-breaking spaces(160) which can be hard to get rid of.

David McRitchie has a TRIMALL macro that looks for the non-breaking space (160)
along with other crap in cells.

http://www.mvps.org/dmcritchie/excel/join.htm#trimall


Gord Dibben MS Excel MVP

On Mon, 1 May 2006 12:59:00 -0600, "Craig Fletcher" <[email protected]>
wrote:

>I have some data that I copied out from a webpage (a credit card summary)
>which I have since added several other columns around it (i.e., I would much
>rather not have to paste it again) and the numbers won't act like numbers.
>I've tried copying them out, and doing "paste special" into a new worksheet
>with all of the different "paste special" options, and I have also tried all
>of the formatting options (currency, numbers, etc.) and still they will not
>be treated as numbers by Excel.
>
>Is there anything else I can try?
>
>Thanks,
>Craig
>

Gord Dibben MS Excel MVP

[Craig Fletcher]

Also,

I went to that same site and tried the paste again, and noticed that the
format it wants to paste into Excel with is "General".


"kassie" <[email protected]> wrote in message
news:[email protected]...
> Insert a 1 in an unused cell. Press <Ctrl><C>. Now select the range
> where
> your numbers are not treated as numbers, then right click, select Paste
> Special, and tick the Multiply option.
>
> "Craig Fletcher" wrote:
>
>> I have some data that I copied out from a webpage (a credit card summary)
>> which I have since added several other columns around it (i.e., I would
>> much
>> rather not have to paste it again) and the numbers won't act like
>> numbers.
>> I've tried copying them out, and doing "paste special" into a new
>> worksheet
>> with all of the different "paste special" options, and I have also tried
>> all
>> of the formatting options (currency, numbers, etc.) and still they will
>> not
>> be treated as numbers by Excel.
>>
>> Is there anything else I can try?
>>
>> Thanks,
>> Craig
>>
>>
>>

[Craig Fletcher]

Thanks Gord...

I apologize, but I don't know what you mean by 'copy an empty cell and
select the "numbers", because if the cell is empty, there are no numbers,
right? Please help me understand, I think we are just miscommunicating.

I really appreciate your time


"Gord Dibben" <gorddibbATshawDOTca> wrote in message
news:[email protected]...
> Craig
>
> The numbers are probably copied in as text.
>
> Re-formatting alone will not change them.
>
> Try this method...................
>
> Format all cells to General.
>
> Copy an empty cell and selet the "numbers"
>
> Paste Special>Add>OK>Esc
>
> That should change the "numbers" into real numerics.
>
> If no joy, there could be spaces before or after the numbers.
>
> These could be non-breaking spaces(160) which can be hard to get rid of.
>
> David McRitchie has a TRIMALL macro that looks for the non-breaking space
> (160)
> along with other crap in cells.
>
> http://www.mvps.org/dmcritchie/excel/join.htm#trimall
>
>
> Gord Dibben MS Excel MVP
>
> On Mon, 1 May 2006 12:59:00 -0600, "Craig Fletcher"
> <[email protected]>
> wrote:
>
>>I have some data that I copied out from a webpage (a credit card summary)
>>which I have since added several other columns around it (i.e., I would
>>much
>>rather not have to paste it again) and the numbers won't act like numbers.
>>I've tried copying them out, and doing "paste special" into a new
>>worksheet
>>with all of the different "paste special" options, and I have also tried
>>all
>>of the formatting options (currency, numbers, etc.) and still they will
>>not
>>be treated as numbers by Excel.
>>
>>Is there anything else I can try?
>>
>>Thanks,
>>Craig
>>
>
> Gord Dibben MS Excel MVP

[Craig Fletcher]

Ah-ha... the cells I pasted in here do have spaces after the "numbers" (that
won't act like numbers). I tried the TRIM function in Excel but the spaces
won't go away. I'm not very familiar with VB or Macros, but I saw the site
and I am going to try some more.

Thanks for the tip....

"Gord Dibben" <gorddibbATshawDOTca> wrote in message
news:[email protected]...
> Craig
>
> The numbers are probably copied in as text.
>
> Re-formatting alone will not change them.
>
> Try this method...................
>
> Format all cells to General.
>
> Copy an empty cell and selet the "numbers"
>
> Paste Special>Add>OK>Esc
>
> That should change the "numbers" into real numerics.
>
> If no joy, there could be spaces before or after the numbers.
>
> These could be non-breaking spaces(160) which can be hard to get rid of.
>
> David McRitchie has a TRIMALL macro that looks for the non-breaking space
> (160)
> along with other crap in cells.
>
> http://www.mvps.org/dmcritchie/excel/join.htm#trimall
>
>
> Gord Dibben MS Excel MVP
>
> On Mon, 1 May 2006 12:59:00 -0600, "Craig Fletcher"
> <[email protected]>
> wrote:
>
>>I have some data that I copied out from a webpage (a credit card summary)
>>which I have since added several other columns around it (i.e., I would
>>much
>>rather not have to paste it again) and the numbers won't act like numbers.
>>I've tried copying them out, and doing "paste special" into a new
>>worksheet
>>with all of the different "paste special" options, and I have also tried
>>all
>>of the formatting options (currency, numbers, etc.) and still they will
>>not
>>be treated as numbers by Excel.
>>
>>Is there anything else I can try?
>>
>>Thanks,
>>Craig
>>
>
> Gord Dibben MS Excel MVP

[kassie]

I think you'll have to use Dave McRitchies's tool to fix your text! as
suggested by Gord Dibben! Sounds like you definitely have some spaces in
there.

"Craig Fletcher" wrote:

> Also,
>
> I went to that same site and tried the paste again, and noticed that the
> format it wants to paste into Excel with is "General".
>
>
> "kassie" <[email protected]> wrote in message
> news:[email protected]...
> > Insert a 1 in an unused cell. Press <Ctrl><C>. Now select the range
> > where
> > your numbers are not treated as numbers, then right click, select Paste
> > Special, and tick the Multiply option.
> >
> > "Craig Fletcher" wrote:
> >
> >> I have some data that I copied out from a webpage (a credit card summary)
> >> which I have since added several other columns around it (i.e., I would
> >> much
> >> rather not have to paste it again) and the numbers won't act like
> >> numbers.
> >> I've tried copying them out, and doing "paste special" into a new
> >> worksheet
> >> with all of the different "paste special" options, and I have also tried
> >> all
> >> of the formatting options (currency, numbers, etc.) and still they will
> >> not
> >> be treated as numbers by Excel.
> >>
> >> Is there anything else I can try?
> >>
> >> Thanks,
> >> Craig
> >>
> >>
> >>
>
>
>

[gailann]

Have you tried Data>Text to Columns?

[Gary''s Student]

If you feel comfortable with VBA, you can try:

Sub numerify()
Dim r As Range
Count = 0
For Each r In ActiveSheet.UsedRange
If Application.IsText(r.Value) Then
If IsNumeric(r.Value) Then
r.Value = 1# * r.Value
r.NumberFormat = "General"
Count = Count + 1
End If
End If
Next
MsgBox (Count & " cells changed")
End Sub


--
Gary's Student


"Craig Fletcher" wrote:

> Ah-ha... the cells I pasted in here do have spaces after the "numbers" (that
> won't act like numbers). I tried the TRIM function in Excel but the spaces
> won't go away. I'm not very familiar with VB or Macros, but I saw the site
> and I am going to try some more.
>
> Thanks for the tip....
>
> "Gord Dibben" <gorddibbATshawDOTca> wrote in message
> news:[email protected]...
> > Craig
> >
> > The numbers are probably copied in as text.
> >
> > Re-formatting alone will not change them.
> >
> > Try this method...................
> >
> > Format all cells to General.
> >
> > Copy an empty cell and selet the "numbers"
> >
> > Paste Special>Add>OK>Esc
> >
> > That should change the "numbers" into real numerics.
> >
> > If no joy, there could be spaces before or after the numbers.
> >
> > These could be non-breaking spaces(160) which can be hard to get rid of.
> >
> > David McRitchie has a TRIMALL macro that looks for the non-breaking space
> > (160)
> > along with other crap in cells.
> >
> > http://www.mvps.org/dmcritchie/excel/join.htm#trimall
> >
> >
> > Gord Dibben MS Excel MVP
> >
> > On Mon, 1 May 2006 12:59:00 -0600, "Craig Fletcher"
> > <[email protected]>
> > wrote:
> >
> >>I have some data that I copied out from a webpage (a credit card summary)
> >>which I have since added several other columns around it (i.e., I would
> >>much
> >>rather not have to paste it again) and the numbers won't act like numbers.
> >>I've tried copying them out, and doing "paste special" into a new
> >>worksheet
> >>with all of the different "paste special" options, and I have also tried
> >>all
> >>of the formatting options (currency, numbers, etc.) and still they will
> >>not
> >>be treated as numbers by Excel.
> >>
> >>Is there anything else I can try?
> >>
> >>Thanks,
> >>Craig
> >>
> >
> > Gord Dibben MS Excel MVP
>
>
>

[Craig Fletcher]

Student O Gary:

This worked great.. I just had to read about how to set up a macro. Good
stuff, thanks much!!

It's weird that TRIM didn't do it, but I don't know enough about this to
understand why.

Craig


"Gary''s Student" <[email protected]> wrote in message
news:[email protected]...
> If you feel comfortable with VBA, you can try:
>
> Sub numerify()
> Dim r As Range
> Count = 0
> For Each r In ActiveSheet.UsedRange
> If Application.IsText(r.Value) Then
> If IsNumeric(r.Value) Then
> r.Value = 1# * r.Value
> r.NumberFormat = "General"
> Count = Count + 1
> End If
> End If
> Next
> MsgBox (Count & " cells changed")
> End Sub
>
>
> --
> Gary's Student
>
>
> "Craig Fletcher" wrote:
>
>> Ah-ha... the cells I pasted in here do have spaces after the "numbers"
>> (that
>> won't act like numbers). I tried the TRIM function in Excel but the
>> spaces
>> won't go away. I'm not very familiar with VB or Macros, but I saw the
>> site
>> and I am going to try some more.
>>
>> Thanks for the tip....
>>
>> "Gord Dibben" <gorddibbATshawDOTca> wrote in message
>> news:[email protected]...
>> > Craig
>> >
>> > The numbers are probably copied in as text.
>> >
>> > Re-formatting alone will not change them.
>> >
>> > Try this method...................
>> >
>> > Format all cells to General.
>> >
>> > Copy an empty cell and selet the "numbers"
>> >
>> > Paste Special>Add>OK>Esc
>> >
>> > That should change the "numbers" into real numerics.
>> >
>> > If no joy, there could be spaces before or after the numbers.
>> >
>> > These could be non-breaking spaces(160) which can be hard to get rid
>> > of.
>> >
>> > David McRitchie has a TRIMALL macro that looks for the non-breaking
>> > space
>> > (160)
>> > along with other crap in cells.
>> >
>> > http://www.mvps.org/dmcritchie/excel/join.htm#trimall
>> >
>> >
>> > Gord Dibben MS Excel MVP
>> >
>> > On Mon, 1 May 2006 12:59:00 -0600, "Craig Fletcher"
>> > <[email protected]>
>> > wrote:
>> >
>> >>I have some data that I copied out from a webpage (a credit card
>> >>summary)
>> >>which I have since added several other columns around it (i.e., I would
>> >>much
>> >>rather not have to paste it again) and the numbers won't act like
>> >>numbers.
>> >>I've tried copying them out, and doing "paste special" into a new
>> >>worksheet
>> >>with all of the different "paste special" options, and I have also
>> >>tried
>> >>all
>> >>of the formatting options (currency, numbers, etc.) and still they will
>> >>not
>> >>be treated as numbers by Excel.
>> >>
>> >>Is there anything else I can try?
>> >>
>> >>Thanks,
>> >>Craig
>> >>
>> >
>> > Gord Dibben MS Excel MVP
>>
>>
>>

[Gary''s Student]

You are very welcome.
--
Gary's Student


"Craig Fletcher" wrote:

> Student O Gary:
>
> This worked great.. I just had to read about how to set up a macro. Good
> stuff, thanks much!!
>
> It's weird that TRIM didn't do it, but I don't know enough about this to
> understand why.
>
> Craig
>
>
> "Gary''s Student" <[email protected]> wrote in message
> news:[email protected]...
> > If you feel comfortable with VBA, you can try:
> >
> > Sub numerify()
> > Dim r As Range
> > Count = 0
> > For Each r In ActiveSheet.UsedRange
> > If Application.IsText(r.Value) Then
> > If IsNumeric(r.Value) Then
> > r.Value = 1# * r.Value
> > r.NumberFormat = "General"
> > Count = Count + 1
> > End If
> > End If
> > Next
> > MsgBox (Count & " cells changed")
> > End Sub
> >
> >
> > --
> > Gary's Student
> >
> >
> > "Craig Fletcher" wrote:
> >
> >> Ah-ha... the cells I pasted in here do have spaces after the "numbers"
> >> (that
> >> won't act like numbers). I tried the TRIM function in Excel but the
> >> spaces
> >> won't go away. I'm not very familiar with VB or Macros, but I saw the
> >> site
> >> and I am going to try some more.
> >>
> >> Thanks for the tip....
> >>
> >> "Gord Dibben" <gorddibbATshawDOTca> wrote in message
> >> news:[email protected]...
> >> > Craig
> >> >
> >> > The numbers are probably copied in as text.
> >> >
> >> > Re-formatting alone will not change them.
> >> >
> >> > Try this method...................
> >> >
> >> > Format all cells to General.
> >> >
> >> > Copy an empty cell and selet the "numbers"
> >> >
> >> > Paste Special>Add>OK>Esc
> >> >
> >> > That should change the "numbers" into real numerics.
> >> >
> >> > If no joy, there could be spaces before or after the numbers.
> >> >
> >> > These could be non-breaking spaces(160) which can be hard to get rid
> >> > of.
> >> >
> >> > David McRitchie has a TRIMALL macro that looks for the non-breaking
> >> > space
> >> > (160)
> >> > along with other crap in cells.
> >> >
> >> > http://www.mvps.org/dmcritchie/excel/join.htm#trimall
> >> >
> >> >
> >> > Gord Dibben MS Excel MVP
> >> >
> >> > On Mon, 1 May 2006 12:59:00 -0600, "Craig Fletcher"
> >> > <[email protected]>
> >> > wrote:
> >> >
> >> >>I have some data that I copied out from a webpage (a credit card
> >> >>summary)
> >> >>which I have since added several other columns around it (i.e., I would
> >> >>much
> >> >>rather not have to paste it again) and the numbers won't act like
> >> >>numbers.
> >> >>I've tried copying them out, and doing "paste special" into a new
> >> >>worksheet
> >> >>with all of the different "paste special" options, and I have also
> >> >>tried
> >> >>all
> >> >>of the formatting options (currency, numbers, etc.) and still they will
> >> >>not
> >> >>be treated as numbers by Excel.
> >> >>
> >> >>Is there anything else I can try?
> >> >>
> >> >>Thanks,
> >> >>Craig
> >> >>
> >> >
> >> > Gord Dibben MS Excel MVP
> >>
> >>
> >>
>
>
>

[Gord Dibben]

Craig

I see from subsequent posts that you got straightened out.

Copy an empty cell means select any empty cell and Edit>Copy.

Select the numbers means select the range of cells that contain the problem
data.

Then Edit>Paste Special>Add>OK>Esc.


Gord

On Mon, 1 May 2006 13:19:38 -0600, "Craig Fletcher" <[email protected]>
wrote:

>Thanks Gord...
>
>I apologize, but I don't know what you mean by 'copy an empty cell and
>select the "numbers", because if the cell is empty, there are no numbers,
>right? Please help me understand, I think we are just miscommunicating.
>
>I really appreciate your time