flip negative sign

[Guest]

Using copy paste from AS400 to excel spreadsheet negative numbers do not read
negative because the negative sign remains on the right side of the number in
excel. Is there an easy way to flip the negative sign from the right side of
the number to the left side?

 

[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

 

[Guest]

Hi

I use a free add-in from www.asap-utilities.com for this job. There are
loads of excellent (and useful) functions in the pack.

Andy.

 

[Guest]

I suspect the AS/400 data is formatted as TEXT in Excel so you will have to
remove the "-" (minus) and then convert to a number.

if A1=1234-

Then in B1 put:

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

B1=-1234

If the data is in one column, you insert a helper column and copy a formula
down and then delete original column.

To cater for positive numbers, use:

=IF(RIGHT(A1,1)="-",SUBSTITUTE(A1,"-","")*-1,A1)

Test first!

 

[Guest]

It works...thank you for that and the link for macro newbies.

 

[Gord Dibben]

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

Checkmark "trailing minus for negative numbers".


Gord Dibben MS Excel MVP

 

[Guest]

Great. Thank you...you wouldn't happen to have the solution or at least an
attempt at the subtotal loop question proposed on 3/9 also. No one has
responded yet. I might need to dangle a carrot out there or something.
Surely it's doable. Thanks again.