change negative sign from end of the number to the begining

[james]

Hi,

I have a column of numbers. Some of the negative number with negative sigh
at the end of the number. How to I convert the negative sign from back of the
number to the front of the number. ( x- -> -x)

Thanks

James

 

[Dave F]

Type 1 in an empty cell in your spread sheet. Copy it then select the column
of numbers in question, paste special, multiply.

This multiplies all values by positive 1 (so positive values remain positive
and negative values remain negative) and all your negative numbers should be
formatted consistently.

Dave

 

[james]

Dave,

I tried your method, but it did not work.

Type 1 in an empty cell in your spread sheet. Copy it then select the column
of numbers in question, paste special, multiply.

This multiplies all values by positive 1 (so positive values remain positive
and negative values remain negative) and all your negative numbers should be
formatted consistently.

Dave

 

[Dave F]

How are the numbers formatted? Right-click on one of the numbers which has
the negative sign to the right and see what its formatting looks like. See
if you can select formatting with the sign on the left.

Dave

 

[james]

Dave,

The format said the cell did not have specific format. I am not able to
convert to regular negative sign.

 

[Dave Peterson]

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

 

[RagDyer]

I would guess that perhaps your numbers are really text that just look like
numbers.

You could try this text formula to *both* move the minus sign and coerce the
text to numbers:

=IF(ISERR(A1*1),LEFT(A1,LEN(A1)-1)*-1,A1)

 

[james]

Thanks Dave,

It works great.

James

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

 

[Gord Dibben]

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

Make sure the "trailing minus etc." option is checked and Finish.


Gord Dibben MS Excel MVP

 

[RagDyer]

What version Gord?

 

[Dave Peterson]

xl2002+
(IIRC)

What version Gord?

 

[RagDyeR]

Been working on the older machines too much!

Thanks Dave.
--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

xl2002+
(IIRC)

What version Gord?
--
Regards,

RD

-------------------------------------------------------------------------- -
Please keep all correspondence within the NewsGroup, so all may benefit !
-------------------------------------------------------------------------- -