Convert positive numbers to negative

[Anita]

Hi

Is there a function which converts positive numbers to
negative numbers? The data was imported from a database
and was typed in like this ...

123-
456-
789-

I used the text to columns option to remove the minus at
the end of the figure but need the figures to be negative
numbers.

Any help appreciated.

 

[Andy Brown]

Try putting -1 in a free cell & copying it. Then select the number range and
Edit -- Paste Special -- Multiply.

Rgds,
Andy

 

[Franmk Kabel]

Hi
one way:
- isnert '-1' in an empty cell and copy this cell
- select your imported figures
- goto 'Edit - Paste Special' and choose 'Multiply'

 

[Guest]

Hi

Try using this:
=-VALUE(LEFT(A2,LEN(A2)-1))
which you can then fill down. Once you are happy with the result, select the
range and Edit / Copy Edit / Paste Special / Values to replace the formulas
with their values.

 

[Guest]

It actually works without the VALUE function:
=-LEFT(A2,LEN(A2)-1)

 

[Gord Dibben]

Anita

VBA Macro solution.

Skips the Data>Text to Columns and the enter -1 solution provided by Frank and
Andy.

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

 

[Ron Rosenfeld]

Hi

Is there a function which converts positive numbers to
negative numbers? The data was imported from a database
and was typed in like this ...

123-
456-
789-

I used the text to columns option to remove the minus at
the end of the figure but need the figures to be negative
numbers.

Any help appreciated.

If you have a later version of Excel, on Step 3 of the Text to Columns wizard,
in the upper left panel, there is a button labeled "Advanced". If you check
this, you will find that one of the options you can select is "trailing minus
for negative numbers". Then the conversion will be automagic.


--ron

 

[Guest]

Thank you everyone !!!!!!!!!