Switching negative signs from back to front

[tracytracy123]

Our system reports unfortunately have negative signs in back of numbers
instead of in front. When we export them to Excel, the negative numbers seem
to be ignored in totals and we are manually moving the negative signs from
back to front to get our totals correct. Is there a way to do this faster?

 

[Don Guillett]

Sub moveminustofront()
For Each c In Selection
If Right(c, 1) = "-" Then
c.Value = "-" & Left(c, Len(c) - 1)
End If
Next c
End Sub

 

[Jacquesvv]

Hi tracytracy123

Assuming the imported values are now seen as a character (left aligned) I
would suggest sorting so all the characters are together and then assuming
the value 123- is in cell c21 the following formula should sort it
=(LEFT(C21,LEN(C21)-1))*-1
Hope this helps

 

[Jim Thomlinson]

Check out this link...

http://www.mcgimpsey.com/excel/postfixnegatives.html

 

[Dave Peterson]

Depending on how you're importing the data, you may see the text to columns
wizard.

And in xl2003 (for sure, maybe xl2002???) and greater, you'll see an advanced
button on step 3 of the wizard.

There's an option to treat those trailing minuses as negative numbers.

If your data is already in place, you can use data|text to columns and start
that same wizard (again xl2003 (or xl2002???) or later.