How to change an amount, 523- to -523 in an easy way?

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.
--
Kevin Backmann


"Karin" wrote:

How to change an amount, 523- to -523 in an easy way?

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?
What does LEN means?




"Kevin B" skrev:

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.
--
Kevin Backmann


"Karin" wrote:

How to change an amount, 523- to -523 in an easy way?

Assume your data in A1

In B1: =SUBSTITUTE(A1,"-","")*-1


Karin" wrote:

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?
What does LEN means?




"Kevin B" skrev:

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.
--
Kevin Backmann


"Karin" wrote:

How to change an amount, 523- to -523 in an easy way?

Another way...

=-SUBSTITUTE(A1,"-","")


"Karin" wrote:

How to change an amount, 523- to -523 in an easy way?

DataText to ColumnsNextNextAdvanced.

Make sure "trailing minus" is enabled then Finish.


Gord Dibben MS Excel MVP

On Thu, 17 Apr 2008 05:52:03 -0700, Karin
wrote:

How to change an amount, 523- to -523 in an easy way?

Yeeeaaaahhh! Thank you!!!

"Gord Dibben" skrev:

DataText to ColumnsNextNextAdvanced.

Make sure "trailing minus" is enabled then Finish.


Gord Dibben MS Excel MVP

On Thu, 17 Apr 2008 05:52:03 -0700, Karin
wrote:

How to change an amount, 523- to -523 in an easy way?

"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.

"Gord Dibben" wrote:

DataText to ColumnsNextNextAdvanced.

Make sure "trailing minus" is enabled then Finish.


Gord Dibben MS Excel MVP

On Thu, 17 Apr 2008 05:52:03 -0700, Karin
wrote:

How to change an amount, 523- to -523 in an easy way?

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,
' = = = = = = = = = = = = = = = =
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

SCGRL wrote:

"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.

"Gord Dibben" wrote:

DataText to ColumnsNextNextAdvanced.

Make sure "trailing minus" is enabled then Finish.


Gord Dibben MS Excel MVP

On Thu, 17 Apr 2008 05:52:03 -0700, Karin
wrote:

How to change an amount, 523- to -523 in an easy way?



--

Dave Peterson