Change of negative amount

K

Kevin B

Insert a helper column to the right of the values you want to transform and
in the first cell to the right of the first value, enter the following
formula modifying the A1 cell address to the cell address of your first value:

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

Copy down the entire length of the column.

Then select all the formula values in the helper column and copy them
(Ctrl+C). Move the first cell of the original values column, click EDIT in
the menu and select PASTE SPECIAL/VALUES.

YOu can now delete the helper column.

Hope this helps.
 
K

Karin

Hi
Thanks for answer. I don't understand "> Then select all the formula values
in the helper column and copy them
(Ctrl+C). Move the first cell of the original values column, click EDIT in
the menu and select PASTE SPECIAL/VALUES." though. Can you please help me again?
Click to expand...
What does LEN means?




"Kevin B" skrev:
 
G

Gord Dibben

Data>Text to Columns>Next>Next>Advanced.

Make sure "trailing minus" is enabled then Finish.


Gord Dibben MS Excel MVP
 
K

Karin

Yeeeaaaahhh! Thank you!!!

"Gord Dibben" skrev:
Data>Text to Columns>Next>Next>Advanced.

Make sure "trailing minus" is enabled then Finish.


Gord Dibben MS Excel MVP
Click to expand...
 
S

SCGRL

"trailing minus" - option is it available on excel 2000? if so do I have to
turn on..I do not have it under "Advance"

Thank you.
 
D

Dave Peterson

Nope. It was added in xl2002 (I think).

But can you use a macro?

Dana DeLouis posted this:


Sub TrailingMinus()
' = = = = = = = = = = = = = = = =
' Use of CDbl suggested by Peter Surcouf
' Program by Dana DeLouis, [email protected]
' = = = = = = = = = = = = = = = =
Dim rng As Range
Dim bigrng As Range

On Error Resume Next
Set bigrng = Cells.SpecialCells(xlConstants, xlTextValues).Cells
If bigrng Is Nothing Then Exit Sub

For Each rng In bigrng.Cells
rng = CDbl(rng)
Next
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
Top