Reposition Characters Within A Cell

**christophercdavis** · 03-28-2011, 12:42 PM

I have been supplied with a file that contains about five or six columns (and several thousand rows) of customer balances and credits, the data from which will be imported into my company's accounting software. The files I've been given identify credit values with a 'Minus' sign (-), which my system needs to have...but the symbol is located on the far right of the value. For example: a credit of $100 would display as

100-

What I NEED to have is a way to reposition those minus-signs to the LEFT side of the value, so that it reads

-100

instead. I can do a 'Find' function to locate the handful of instances where this occurs and manually change the symbol's location, but I'd much rather let Excel do the heavy lifting for me.

Thanks!

**NBVC** · 03-28-2011, 12:56 PM

To do it with formulas try this:

=IF(ISNUMBER(A1+0),A1,"-"&LEFT(A1,LEN(A1)-1))+0

copied down

where A1 contains first number in Column A.

You can make a new helper column with this formula for each of the columns needing fixes.

Then you can copy those helper columns and paste special >> Values over the originals and then delete the helper columns.