Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old November 26th 04, 04:33 PM
Scott
 
Posts: n/a
Default Converting text to numbers

I have a data file I'm reformatting that has all the negative numbers with
the negative sign on the right side thereby Excel sees these as text. I used
a formula last week that converts these to the correct negative number but
seemed to have misplaced the formula.

Anyone help me with this trick?

  #2   Report Post  
Old November 26th 04, 05:23 PM
Norman Jones
 
Posts: n/a
Default

Hi Scott'

One way:

=LEFT(A1,LEN(A1)-1)*-1

---
Regards,
Norman



"Scott" wrote in message
...
I have a data file I'm reformatting that has all the negative numbers with
the negative sign on the right side thereby Excel sees these as text. I
used
a formula last week that converts these to the correct negative number but
seemed to have misplaced the formula.

Anyone help me with this trick?



  #3   Report Post  
Old November 26th 04, 06:00 PM
Myrna Larson
 
Posts: n/a
Default

Assuming you have a mix of both positive and negative numbers, in the helper
column use this formula:

=IF(RIGHT(A1)="-",LEFT(A1,LEN(A1)-1)*-1,A1)


On Fri, 26 Nov 2004 08:33:03 -0800, "Scott"
wrote:

I have a data file I'm reformatting that has all the negative numbers with
the negative sign on the right side thereby Excel sees these as text. I used
a formula last week that converts these to the correct negative number but
seemed to have misplaced the formula.

Anyone help me with this trick?


  #4   Report Post  
Old November 26th 04, 09:17 PM
Gord Dibben
 
Posts: n/a
Default

Scott

If using Excel 2002 or 2003........

DataText to ColumnsNextNextAdvanced Check "trailing minus for negative
numbers"

Macro............

Sub ChangeSign()
Dim Cell As Range
''to move a - sign from right to left in Column A
On Error Resume Next
For Each Cell In Range(Cells(1, 1), _
Cells(Rows.Count, 1).End(xlUp)). _
SpecialCells(xlConstants, xlTextValues)
If Right(Trim(Cell.Value), 1) = "-" Then
Cell.Value = CDbl(Cell.Value)
End If
Next
On Error GoTo 0
End Sub

OR for entire sheet...........

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

On Fri, 26 Nov 2004 08:33:03 -0800, "Scott"
wrote:

I have a data file I'm reformatting that has all the negative numbers with
the negative sign on the right side thereby Excel sees these as text. I used
a formula last week that converts these to the correct negative number but
seemed to have misplaced the formula.

Anyone help me with this trick?




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I rotate a text box Kent Charts and Charting in Excel 2 December 17th 04 12:13 AM
How can I asign a number value to a text line in Excel? AIF_GoofyDo2 Charts and Charting in Excel 0 December 6th 04 01:19 AM
Why do my text boxes disappear from my chart when I click out? Robboo Charts and Charting in Excel 1 November 27th 04 05:49 PM
Negative Numbers Glenda Excel Discussion (Misc queries) 3 November 26th 04 02:06 PM
How to count matching text Duplicateman Excel Discussion (Misc queries) 6 November 26th 04 08:40 AM


All times are GMT +1. The time now is 09:43 AM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017