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

 

[Guest]

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]

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

 

[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

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".

 

[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

 

[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....

 

[Guest]

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.

 

[gailann]

Have you tried Data>Text to Columns?

 

[Guest]

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

 

[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

 

[Guest]

You are very welcome.
--
Gary's Student

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

 

[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