Change "-" from right first character to left first character to make a negative amount

[amorrison2006]

Hi

I have a column of data.


Unfortunately the negative symbol is on the right of the numbers to
represent a negative number.


What I need is a macro to look down a column and if it find the first
character to the right of that cell is a "-" symbol then move that to
the first character on the left of the cell to make the numbers
negative.


I hope this is possible and someone can help,


Thank you so much in advance,

Andrea

 

[Guest]

I believe you can use Text-to-Columns if you have Excel 2000 or later.

Try this:

Select the column range of cells to be impacted

From the Excel main menu:
<data><text-to-columns>
.....click [Next] until Step 3 of 3

Click the [Advanced] button
.....Check: "Trailing minus sign for negative numbers"....Click [OK]

Click [Finish]

Does that help?
***********
Regards,
Ron

XL2003, WinXP

 

[Guest]

Here is one VBA solution that ignores values unless they contain a dash
(minus
sign). Using this method, you need to make your selection first, then run.
Watch for line wrapping:

Dim lDash As Long
Dim sValue As String
Dim rCell As Range
For Each rCell In Selection
If InStr(1, rCell.FormulaR1C1, "-", vbTextCompare) <> 0 Then
lDash = InStr(1, rCell.FormulaR1C1, "-", vbTextCompare)
sValue = Application.WorksheetFunction.Replace(rCell.FormulaR1C1,
lDash, 1, "")
rCell.FormulaR1C1 = "-" & sValue
End If
Next rCell

HTH