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,

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,

- Minus sign at the end of number
- Read a number with a blank space at the end
- Removing Minus sign at the end of a value
- Macro to convert OMNIStations negatives to Excel format
- I want to add a plus sign behind a number and keep the leading zer
- Converting Numbers with Trailing Minus Signs to Normal Negative Numbers
- Putting a minus sign in front of numbers
- Negative numbers in parenthesis if not shown in drop down box
- "What if" Scenarios used twice. With an adding sum at the end.
- How do I prefix a worksheet of numbers with a Minus (-) Sign
- Negative number formatting
- Need macro to put minus sign in front of numbers
- Removing a plus sign?
- Formatting: Display a minus sign before a number
- Strip minus signs from right to left
- Add a minus sign
- Customise Accounting Format
- Adding a new row, hyperlink and sheet with a button
- Delete empty spaces at the end of text of a column?
- Extract and sum numbers from a text string
- Zero appears at the end of a string of 16 digits. Two zeros after
- I want to get brackets round my figures not minus signs
- How to add colum at the end of table using VBA.
- I want to add a checkbox at the end to return data to a separate s

Thanks a lot,

Cope

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

and to place the minus sign in front of it for eg -175000

Your assistance will be most appreciated

Howard

' Keyboard Shortcut: Ctrl+a ' ActiveCell.FormulaR1C1 = " $10,236.60" Range("D13").Select End SubThat 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?

excel to a software package. When I add the + or - at the end of the number,

it deletes the leading zeros.

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))

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.

box does not show negative numbers in parenthesis. My spreadsheet shows

negative numbers with a '-' minus sign.

Thank you

Sheron

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.

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

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 ]

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.

have a spreadsheet that was created by someone else, and it does this, but I

cannot figure out how to do it!

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.

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 SubI have tried the above, however it adds it to the end. 123456-

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

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

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

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

digit number. Two zeros appear at the end of 17. What is causing this?

do this? As when I attempt to it automatically converts it to a minus sign

can anyone provide me with the vba code for this.

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.