does not recognize numbers

  • Thread starter Thread starter dan
  • Start date Start date
D

dan

When I copy a table from Schwab and paste it into Excel
2000 it looks fine. However, the numbers cannot be used
in any calculation. I can't format the data as numbers
(eg I can't change the number of decimal places, can't
make it currency etc) but I can change the font and other
aspects. I can't add any cells. They seem to have a
numerical value of zero. I can copy and paste to other
cells but it still does not work like a number. It shows
up in the cell and the formula line like normal numbers.
If I put in new numerical data, that data works fine. I
tried to convert the text to numbers as per the help file
(setting a cell equal to 1 and then copying the format to
the affected cells, but it doesn't do anything.
 
I have found a document in Microsoft assistance that
tells how to use the formula bar to use a VALUE but it
does not make sense to me. Maybe if that is the answer
to my problem, someone can make it more clear to a
novice.
Thank you.
 
Dan, the data you have is of a textual nature, and what need sto be done is to convert it back to
numeric. You cannot simply format the cells as number because unfortunately Excel doesn't work
that way. Usually, the easiest way to accomplish this is to copy an empty cell, then select all
the data concerned and do Edit / Paste Special / Add. This will usually coerce the data back to
numeric, UNLESS, you have garbage in there such as you may get if copying from html pages. If
this is the case you should first go and copy Dave McRitchie's Trimall macro from the following
link, and then run it against your data. Then try the copy empty cell routine:-

http://www.mvps.org/dmcritchie/excel/join.htm#trimall
 
or another way, a bit of Ken's a bit of Harald's..

Put the value 1 in a cell, and copy to the clipboard. Select all the text
cells, goto menu Edit>PasteSpecial and click the Multiply button.
 
another
Sub FixRangeValues()
For Each C In Selection
C.Value = Format(C, "00")
Next
End Sub
 
Thank you Ken. The link you gave me allowed me to copy
my first macro and use it. It did the trick! The
instructions at the beginning of the link were especially
helpful.

Thank you Don and Harald. I guess your fixes were good
too but I didn't need to 'go there'.

Bob,
That was the fix that the Help section suggested but it
did not have the desired effect.

THANK YOU ALL FOR GIVING ME A SUNDAY! I was starting to
run this large spreadsheet through a OCR to capture the
data!
 
Hi Dan,
First of all thanks for telling us that you found the solution which works
for your and which one(s) in particular. This helps the posters as
well as other people looking for answers which type of answers
work best or are maybe just easier to understand or implement.

Glad the link to my page worked for you, and especially pleased that
you looked at the entire page. Now that you have a working solution,
I would reexamine the other solutions there may be some things you
may need or want to change in the future and a better understanding
of both manual and macro solutions comes easier if you are familiar
with both the problem and some solutions.

Warning: Change one line of code to incorporate something else and
you become a programmer.

BTW, now that you know how to install a macro, you can use the
excel.programming newsgroup when you think you need a
programming solution, some newsgroups can be found in
http://www.mvps.org/dmcritchie/excel/xlnews.htm
 
Back
Top