changing the placement of the negative sign

N

njbeancounter

Board Regular
Joined
Oct 7, 2002
Messages
158
I imported a text file into excel, only to find that the "-" negative sign is placed after the number. Since the file is over 4000 lines, I cannot replace them one by one. I am only interested in the numbers in one column, which is column "m". Also, not every line contains a number, and some of the cells contain positive numbers.

I am trying to learn vba, but my boss is much less patient than I am.
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Sort by date Sort by votes
Hello,

Does this help at all?

Code: 
Sub neg_sign()
For MY_ROWS = 1 To Range("M65536").End(xlUp).Row
If Right(Range("M" & MY_ROWS).Value, 1) = "-" Then
    Range("M" & MY_ROWS).Value = "-" & Left(Range("M" & MY_ROWS), Len(Range("M" & MY_ROWS)) - 1)
End If
Next MY_ROWS
End Sub
 
Upvote 0
Hi,

You could put a formula in an adjascent column...

=IF(A1="","",--(IF(RIGHT(A1)="-","-","")&SUBSTITUTE(A1,"-","")))
 
Upvote 0
Excel 2003's Data|Text to Columns has an option for trailing minus sign, which you can run to resolve the issue.

Otherwise:

In X2 enter & copy down:

=IF(N(M2),M2,-SUBSTITUTE(M2,"-",""))
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Question
copy columns to other work sheet for variable number of rows
Replies
1
Views
209
Watersource
W
D
  • Question
Use formula in sports analysis to check accuracy of predictions which involves if one negative number is more negative than another negative number
Replies
4
Views
436
Micron
M
T
  • Question
Help building a table based on criteria for kids sports program
Replies
0
Views
322
TeachMeEverything
T
C
  • Question
New Part Numbers
Replies
4
Views
224
alansidman
A
C
  • Question
Extract Address information from large text file
Replies
5
Views
407
hayden
H

Forum statistics

Threads
1,214,400
Messages
6,119,289
Members
448,885
Latest member
LokiSonic

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top