What should be a relatively simple Excel question

Say I have a data set, and positive numbers are positive, but negative numbers have the minus sign after the number rather than before. How do I do a mass find replace (ctrl+H) in the data set to move all of these signs to the front of the number?

For example, a cell in my set will read: "5421-", and I want it to read "-5421" without having to go in and manually change each one.

I've been banging my head against a wall for an hour trying to figure out what should be a simple find and replace, but have had no success.

This would save me about half a day's worth of work per month.

Thank you in advance for any advice!

You could use a helper column.

Enter a formula like

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

then drag down formula. Paste Special values and replace old column with the new one

VBA Noob

Hmmm this does work - thank you.

However, despite the minus sign being moved to the front where it should be, the cell does not become a summable value even when I copy and paste special as values into a new cell.

For example, say I have 554 and 554- and then I apply your formula to both cells and then copy and paste those cells as values to a new sheet, and then I attempt to sum them; excel spits out 554 as a sum rather than 0.

Any ideas?

If I use the above formula, and then copy and paste as values into a new sheet, and then export the sheet as a txt file, and then import it back into excel (after inserting page breaks etc) then it works - good enough for me!

Thank you for the help.

Try
=value(IF(RIGHT(A1,1)="-","-"&SUBSTITUTE(A1,"-","",1),A1))

Wow that works perfectly, thank you very much.

This will save me a good chunk of time.