Free Microsoft Excel 2013 Quick Reference

Negative numbers with a minus sign at the end ?

Hi all,

I import data from another software into excel, but many negative numbers have the minus signs at the end, 30-, 700-, 4000-

Can I write a formula to change them into a regular negative numbers -30, or just a positive number? 30, then 30*-1 ?

I need to write the same formula to detect every cell.

Thanks,


Post your answer or comment

comments powered by Disqus
I am exporting data from a software to excel. But I have a problem since all the negative number are written with the negative at the end of the number instead of the beginning (don't ask me why). So what I would like to do is a find and replace to find any number that has a minus sign at the end and put the minus sign at the beginning. Is there any way to do so. By the way, the numbers can be anywhere from A1 to BG20677. So I can't make a macro that go cells by cells to see if there is a minus sign.

Thanks a lot,

Cope

Hi
I have copied a file to excel and when I paste it it appears the numbrer with a blank space at the end so the number cannot be used as a number, So my question is how can i Remove that last blank caracter.
For example "434 " this is a "4" "3" "4" and " "(blank space)
How can I convert this to a number?
Thanks in advance

I need VBA code that will remove a trailing minus sign at the end of a value for eg 175000-
and to place the minus sign in front of it for eg -175000

Your assistance will be most appreciated

Howard

I need to paste data into Excel from OMNIStation to do roll-ups on specific fields. But OMNIStation designates negative numbers with a minus sign at the end of the number (e.g., "$10,236.60-"). So, I tried recording a macro to remove the minus sign at the end and put it at the beginning, at which point Excel then recognizes the number as a negative value. Problem is, Excel doesn't record the keystrokes, rather it is recording the contents of the cell...
' Keyboard Shortcut: Ctrl+a
'
    ActiveCell.FormulaR1C1 = "        $10,236.60"
    Range("D13").Select
End Sub
That means when I run the macro on the next negative value cell it just copies the same value into that cell. Not much help. Is there a way to get Excel to record the actual keystrokes and not treat this as a formula?

I need to have a 15 digit number with a + or - sign at the end to export from
excel to a software package. When I add the + or - at the end of the number,
it deletes the leading zeros.

Problem:

Column A contains an imported list of positive and negative numbers.
For negative numbers, the minus sign appears to the right, rather than to the left.
We want to convert all the numbers with trailing minus signs to normally-signed negative numbers.

Solution:

1. Use the RIGHT and SUBSTITUTE functions as shown in the following formula in column B:
=IF(RIGHT(A2,1)="-",SUBSTITUTE(A2,"-","")*-1,A2)
2. Use the VALUE, IF, RIGHT, LEFT, and LEN functions as shown in the following formula in column C:
=VALUE(IF(RIGHT(A2,1)="-",RIGHT(A2,1)&LEFT(A2,LEN(A2)-1),A2))

Hi,

Could someone please alter the following macro so that if a number in Column E is less than 50, it puts a minus sign at the beginning of the number in the adjacent cell in Column D.

For example, if E2=48 and D2=26, then change D2 to -26.

If I reverse the > sign in the macro, it does work but it adds zero's to all the emply cells in Column D after the last data cell.
---------------------------------
Sub Change_Signage()

For Temp = 1 To 65000
If Range("E" & Temp).Value > 50 Then
Range("D" & Temp) = Abs(Range("D" & Temp).Value) * -1
End If
Next Temp

End Sub
--------------------------

Stewart, if you are reading this, thanks for the macro. I did try to contact you direct to get it fixed but heard nothing and I need this macro to work very urgently. Hence this
request to board members.

How do I get negative numbers in brackets if <Format<Cells<Numbers drop down
box does not show negative numbers in parenthesis. My spreadsheet shows
negative numbers with a '-' minus sign.

Thank you
Sheron

I am having terrible trouble trying to figure out a code for what I am trying to do.
I have used combinations of things on the internet but still can not find a simple way to get the answer I want.

Please please help me as this would resolve a major problem at the end of a 7 day working spread sheet.

Statement:

If cell XX1 was "yes" and cell YY1 is "3" then add one,

To repeat this within a range of cells of two columns and 400 rows. If column XX1 was "yes" and column YY1 was "3" then it would add one to zero. This would repeat for each row.

So if out of 400 6 cells had "yes" and "3" then the total would be 6.

Sounds difficult but hopefully get my drift.

I have a whole worksheet full of positive numbers and would like to reverse
them to negative numbers without having to retype them in. Would anyone know
a shortcut whereby i would be able to prefix all the numbers with minus sign?

Many thanks

This is my first time, I hope I'm in the right place. Our company uses an old mainframe accounting system that shows negative numbers with the minus sign at the end of the number rather than the beginning. When we import the data into Excel for manipulation we have to go to each negative number to change the location of the minus sign so it is recognized as being negative. I'm new to the department and I think there has to be a better way. I've tried data validation, conditional formatting, and a couple of other things with no success. Any ideas?

Hi

I have 2 columns, D and E, which have about 40,000 numbers in each column.

When a number in any cell in Column E is LESS THAN 50, I need the macro to put a minus sign at the beginning of the number in the adjacent cell in Column D.

For example,
If cell E2 is 48 and cell D2 is 252, then the macro needs to change 252 to -252.

If cell E2 is greater than 50 then the value in D2 is not changed.

The numbers in column D range from 1 to 999999

If anyone can help, I would be very grateful.

Regards,
Mark

[ This Message was edited by: Henner on 2002-12-10 07:35 ]

[ This Message was edited by: Henner on 2002-12-10 07:36 ]

All,

I'm pretty average with Excel, so there may be a not too diffictul solution to this issue, but I have turned to a dedicated forum as I simply can't find it...

I have data I've imported into excel. One column was basically numbers, with 5 additional characters at the end of each value. I used the LEFT command to strip those out (yay me and the help file!)

What I'm left with is the numbers. BUT, there is an issue. Every number is preceded either with a - or a + (depending, obviously, on whether the number was negative or positive).

I'd like to just be able to do normal calcuations etc with the numbers, but the + signs are causing obvious grief. I've tried changing the cells to number, to text, to general etc. I also figured I could just do a search and replace, search for a + and replace with nothing, and then convert all the cells to numbers. But that doesn't work. According to the Find item there are no +s in my spreadsheet.

I assume because the + symbol has significance excel tries to treat it differently and that is causing my issue? Is there a way around this?

Oh, and I'm using Excel 2007...

Any help would be greatly appreciated!

Cheers.

How do I format a number so that it display a minus sign before the number. I
have a spreadsheet that was created by someone else, and it does this, but I
cannot figure out how to do it!

When I import to excel from our accounting system, negative numbers show the
minus sign at the right e.g. 100- instead of -100 or (100) Excel reads this
as text and I have to go through the list manually inserting a minus sign at
the left and deleting the minus sign from the right.

Is there any easier way to do this? The numbers are not a fixed length, so
the - could be the third or the thirteenth character in the cell.

Hello,

I am looking for advise to add a minus sign (-) to the beginning of text in a column in our spreadsheet.

For example the column has: 123456

I would like it to be: -123456
I have thousands of lines and each line has a unique text string.

Thanks in advance

HTML Code:
Public Sub X()
 
   Dim c As Range
   '// for each cell in the range you want to edit.
   '// Change A1:A500 to suit ...
   For Each c In Range("A2:A2727")
      '// Already got - at the beginning?
      If Left$(c.Value, 1)  "-" Then
         '// No, add it
         c.Value = c.Value & "-"
      End If
   Next
 
End Sub
I have tried the above, however it adds it to the end. 123456-

Hi everyone,

Can anyone help me with custom formatting of numbers - i am trying to get a version of the "accounting" format with brackets for negatives instead of a minus sign. At the moment i am using the custom format

"£000.00;£ (00,000.00)"

but then I have to vary the number of spaces in every cell depending on how many characters are in the numbers, to get the £ at the left hand side of the cell (and it doesn't exactly line up). Is there an easier way to do this?

Thanks,

Adam

Good afternoon,

I am playing about with VBA in an attempt to improve my excel proficiency but I have a problem that i have been tackling for some time due to its complexity:

I have a workbook with a master sheet containing an overview of data for the subsequent x number of worksheets, each individual worksheet details an in-depth look at the data for each entry on the master sheet. My problem is this:

I would like to use a button on the master sheet to automate a few tasks all in one click:Insert a new row at the 3rd to last row on the master sheet; it can't be the last row as this contains SUM(xx:yy) formulas.Populate this new row with data identical to the row above, complete with formatting too.Have a user input box come up asking for the name of the new entry and overwrite that inputted name to cell Bx where x is the new row number.Insert a new worksheet at the end of x number of sheets and name it. The sheets are named as follows: "Master", "1", "2", "3"....."x" so ideally I would like the new worksheet to be named "x+1".Create a hyperlink at new cell Bx linked to the new worksheet "x+1"Copy and paste the contents of sheet "x" to sheet "x+1"I have had limited success in automating this process in a stepwise fashion but i would like to have the single button do all commands in a single click, whether it be in the order i have listed in the bullet points or not is inconsequential i think. Any help or advice in tackling this problem in part or in whole would be greatly appreciated.

Many thanks in advance

Shaolin1976

I have a column that has different headings that I want to group together. They are entries made by different people, some people for one reason or another add an extra space or 2 with the spacebar after the entry. When grouping these with a pivot table this makes it more difficult as an entry with an extra space at the end won't group with the entry without the space.

Is there a way to take a column that has many text entries and delete any spaces after the last word of them?

Thanks,

~J

Hi

This is my first time posting - so I hope I've picked the correct section to ask for help.....

I have three cells containing the following information;

CLY-7.5 CLY-5.0 CLY-2.0

Sometimes the text string will change, however there will always be a dash and then one number with one decimal point at the end ie;
LSL-5.0 or A/L-6.3 or CLN-4.5 etc...

I would like a formula that will calculate 7.5 + 5 + 2 = 14.5

Can anyone please help?

Many thanks

After I enter 16 digits into a cell, a zero appears at the end of the 16
digit number. Two zeros appear at the end of 17. What is causing this?

i want to put a bracket round my negative numbers not a minus sign how do i
do this? As when I attempt to it automatically converts it to a minus sign

I need to add a new column at the end of the table when ever i run the macro. like i ll name that column as date and have to update corresponding rows with that day data.
can anyone provide me with the vba code for this.

I want to add a checkbox option at the end of the row of data to return
certain, not all data in the row, to a separate file, preferably, or to
separte worksheet. If the box is checked the info from that row will return.


No luck finding an answer? You could always try Google.