Move negative sign help _ Macro

Question

Hi!

I would like to take a negative number -10000 and move the negative sign to the right of the number for my system to accept the amount. Could someone help me with a Macro?

If column G is negative then move the negative sign to the right of the number

If column H is negative then move the negative sign to the right of the number

Please and thanks so much!

Nikki

Answer 1

Nekiaholland,

Perhaps this:

Sub move_sign_right()

Dim c As Range, s As String

For Each c In Selection

    If InStr(1, c.Text, "-", vbTextCompare) Then

       s = c.Text

       s = Replace(s, "-", "")

       s = s & "-"

       c.Value = s

    End If

Next

End Sub

--

Regards,

Tom Ogilvy

Answer 2

Are you doing this because you are building say a CSV file and you are just using Excel to set it up and then export it - or does -Excel- still need to see the number as a number and a Negative number at that? 

so assuming you wanted to export a CSV. then

abc	abc	76-	-56	ABC	-76	76-	22-	ABC
abc	abc	59-	-79	ABC	-59	59-	51-	ABC

I produced column G with :  =ABS(F1)&"-"   and drag filled the formula down.

I produced column H by using the custom number format:  #,##0;#,##0-;0

column C was a paste special values of column G. 

when I exported as CSV I got

abc,abc,76-,-56,ABC,-76,76-,22-,ABC

abc,abc,59-,-79,ABC,-59,59-,51-,ABC

so the right sided hypen/negative sign is preserved whether by formula, stored as text or formatting.

These approaches would be easier than a macro. 

--

Regards,

Tom Ogilvy

Answer 3

So easy! Thanks so much...

Answer 4

Hi Nikki,

Instead of using a macro, why not format the numbers to display the minus sign as you want. Try, for example, a format like:

                      #,##0.00;#,##0.00-

===

Regards,

Norman

Answer 5

Hi, go to format cell, custom and enter

#,##0.00;#,##0.00-