Changing 1234.00- to -1234.00 automatically

L

Lisa

Everyday I have to copy and paste data from a program to an excel
spreadsheet. Positive numbers come in fine, negative numbers don't. They
align to the left of the cell with the negative sign to the right of the
numbers. Currently I: F2, backspace, home, put in the - sign and enter to
the next cell where I repeat this approximately 80 times.
I have tried setting up a macro, a scenerio, and a what if function, but
with no luck on any of them. The macro was my best guess, but it saves the
number along with the steps, so everyday the numbers revert back to the ones
saved in the macro not what they should be.
Does anyone have a solution for quickly changing the format of these numbers
after they are pasted in?
 
G

Gord Dibben

Select the range.

Data>Text to Columns>Next>Next>Advanced. Checkmark in "trailing minus sign for
negative numbers">Finish.

A macro if you wish.

Sub Negsignleft()
Dim cell As Range
Dim rng As Range
''move minus sign from right to left on entire worksheet
On Error Resume Next
Set rng = ActiveSheet.Cells. _
SpecialCells(xlCellTypeConstants, xlTextValues)
On Error GoTo 0
For Each cell In rng
If IsNumeric(cell.Value) Then
cell.Value = CDbl(cell.Value)
End If
Next cell
End Sub


Gord Dibben MS Excel MVP
 
L

Lisa

Yes, the cells are set up to have two decimal places, a comma, and show
negative numbers in red with brackets.
 
L

Lisa

Thank you. This looked so easy, unfortunately when I went to the advanced
step, my only two choices were decimal separator or thousands separator with
drop down answers. There was nothing that said "trailing minus sign for
negative numbers" I tried both with delimited text and fixed width. Work
computers have excel 2000... would this be the issue?
 
G

Gord Dibben

Apologies for not stating the this feature under Text to Columns was introduced
in Excel 2002.

The macro will work in all versions.


Gord

Thank you. This looked so easy, unfortunately when I went to the advanced
step, my only two choices were decimal separator or thousands separator with
drop down answers. There was nothing that said "trailing minus sign for
negative numbers" I tried both with delimited text and fixed width. Work
computers have excel 2000... would this be the issue?

Gord Dibben MS Excel MVP
 
L

Lisa

You have the undying gratitude from everyone in my department! The macro
works perfectly. THANK YOU THANK YOU THANK YOU!!!
 
G

Gord Dibben

Is that a yes or a no<g>

Thanks for the feedback,

Gord

You have the undying gratitude from everyone in my department! The macro
works perfectly. THANK YOU THANK YOU THANK YOU!!!

Gord Dibben MS Excel MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top