Converting an invalid negative number to recognizable format?

[Guest]

An excalibur report printed into an excel table displays its negative numbers
with the negative sign at the end of the number. How can I move all of the
incorrect negative signs to the front of the number without having to move
them all individually?

 

[JE McGimpsey]

See

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

 

[IanKR]

An excalibur report printed into an excel table displays its negative

numbers with the negative sign at the end of the number. How can I
move all of the incorrect negative signs to the front of the number
without having to move them all individually?

Using a worksheet function: put this formula in a temporary "helper column":

=IF(RIGHT(A1,1)="-",LEFT(A1,LEN(A1)-1)*-1,A1)

(adjust cell ref accordingly) and copy down. Then copy / paste values the
cells from the helper column back to the original column.

A similar approach could be done with VBA, without having to use a temporary
helper column.

 

[Gord Dibben]

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

Make sure "Trailing minus etc." is checkemarked.


Gord Dibben MS Excel MVP