Convert number in text format to number

C

Cheryl

I have downloaded a text file into Excel. The nos. are reflected as text and I am not able to perform sum function. eg. negative 4 is shown as 4-. How can I quickly convert it into numbers without having to go to each of them and key the - in front of the nos? I have tried reformat it to numbers to no avail
Thank yo
 
G

Greg Koppel

Set up another column and enter the formula: =value(cell address w/ text)

HTH, Greg

Cheryl said:
I have downloaded a text file into Excel. The nos. are reflected as text
Click to expand...
and I am not able to perform sum function. eg. negative 4 is shown as 4-.
How can I quickly convert it into numbers without having to go to each of
them and key the - in front of the nos? I have tried reformat it to numbers
to no avail.
 
G

Gord Dibben

Cheryl

If Excel version 2002 or newer.......not sure of 2000, but don't think so.

Data>Text to Columns>Next>Next "Advanced". Check "Trailing minus for negative
numbers." Then "Finish".

Alternate.......

Sub Negsignleft()
Dim cell As Range
Dim rng As Range
''move minus sign from right to left on entire worksheet
On Error Resume Next
Set rng = ActiveSheet.Cells. _
SpecialCells(xlCellTypeConstants, xlTextValues)
On Error GoTo 0
For Each cell In rng
If IsNumeric(cell.Value) Then
cell.Value = CDbl(cell.Value)
End If
Next cell
End Sub

Gord Dibben Excel MVP
 
Top