Can't do formulas with numbers...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

When I import the files (i recieved via email) into excel, everything is OK,
except Excell doesn't recognise the numbers in the cells as such and as a
result, it doesn't do formulas.. Anyone know the trick to fix the problem?
 
Maybe,

Select a blank cell and choose Edit|Copy
Select the cells that contain the (text) numbers
Choose Edit|paste special and select Add
Click OK

Mike
 
Tried that. Still nothing. I also tried to fix each number by hand then reset
the cell formating. (which wouldn't work anyway bc i hace abt 300,000 rows
and 15 columns). Still same issue
 
Emoke said:
the cell formating. (which wouldn't work anyway bc i hace abt 300,000 rows
Click to expand...

300,000 rows? I assume you have XL2007. It may be therefore that the menu
references I give are not correct.

It may be that you have non-printing characters in the cells as well as the
number that you can see. Try this on a copy of the data because Replace can
make unwanted changes if you are not careful:

Select that cells that you want to change and then select:

Edit > Replace
In the "Find what:" dialog box enter 0160 *from the number pad* NOT the
numbers above the letters.
Leave the "Replace with:" box empty and press "Replace all"

This will remove all HTML non-breaking spaces

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
You may have blanks or ascii-160 characters in the cells along with the
digits. Try running this small macro:

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
 
A number of tricks:
1 It might be worth trying selecting a blank cell, copy, then select your
"numbers", & Edit/ Paste special/ Add.
2 You might try Data/ Text to columns.
3 If you've got spaces aand/or non-printing characters in your cells with
the "numbers", you might try the TRIM or CLEAN functions.
4 If you are still struggling, you might need to identify the individual
characters in the cell (split with MID) using the CODE function.
5 It might also be worth looking at your input files with a text reader,
perhaps Notepad, to see whether that gives any clues.
 
Back
Top