Numbers as text

  • Thread starter Thread starter Radio848
  • Start date Start date
R

Radio848

Hello again guys:
I am using ME and XL 2000..I am also using Money standard. When I use the
money link to import info from money, it does just exactly what it is
supposed to do. But, the memo field is formatted as text. In money, in the
memo field, when entering a check I put numbers that represent the price per
gallon that the particular purchase represents. Now in XL, when the money
info is imported the memo field is formatted as text. Thus, when I try to do
any calculations based on that cell, I must first copy the info to a
neighboring cell in order to get formulas to work right.
Is there any way to get the formula to recognize the text and convert it to
number format?
Example: cell is in vehicle record... =AVERAGE('Auto Fuel'!I:I,'Auto
Fuel'!M:M); The "I" range ref is the text formatted range.
Any suggestions?
Thanks you-all!!!
 
Try formatting the column as a number.
Then if F2 followed by Enter turns it to a number you're in pretty good
shape. Simply choose an empty cell and copy it. Then select the
recalcitrant column, Edit (menu), Paste Special, Add.

If the F2 followed by Enter does not fix the problem you may have
other characters in there like Char(160). Try installing the
macro TrimALL from
http://www.mvps.org/dmcritchie/excel/join.htm#trimall

If that or perhaps followed by F2 does not fix the problem,
then look at the
http://www.mvps.org/dmcritchie/excel/join.htm#debugformat
to find out what the problem characters are and once you know
that it should be apparent what you want to do.
 
Radio

You could try placing *1 at end of your formula to force a change.

i.e. =AVERAGE('Auto Fuel'!I:I,'AutoFuel'!M:M)*1

OR copy an empty cell.

Select the range of textual numbers and Format as General or Number then Paste
Special>Add>OK>Esc.

Gord Dibben Excel MVP XL2002
 
Back
Top