Change of negative amount

  • Thread starter Thread starter Karin
  • Start date Start date
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.
 
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:
 
Data>Text to Columns>Next>Next>Advanced.

Make sure "trailing minus" is enabled then Finish.


Gord Dibben MS Excel MVP
 
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...
 
"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.
 
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, (e-mail address removed)
' = = = = = = = = = = = = = = = =
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
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

count until blank cell 2
counting duplicates 11
range of Formula copy but not changing the content 4
Filling in missing data 5
Move data from column to rows HELP!!! 5
Ranking if number is between 550 and 855. 2
How can I cross reference information in a table? 1
Change format to mm dd yyyy hh mm ss 1

Back
Top