Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
- signs at back of number so won't add up, please help!
I copy data into excel from an accounts programme, but all the minus signs are at the back so Excel won't add these numbers up i.e 4 3- Th answer comes to 4 rather than one , what can I do about this? Thanks for your help Adam I know you guys/girls know an answer |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
- signs at back of number so won't add up, please help!
Create a helper column to the right of the numbers in and enter the following
formula in the new column, substituting the cell address A1 for the cell address your numbers start in: =IF(RIGHT(A1,1)="-",VALUE(LEFT(A1,LEN(A1)-1)*-1),A1) Copy the formula down the column to get all the numbers. Copy the all the cells in formula column, move to the top of the number column and click EDIT in the menu and select PASTE SPECIAL. Click the VALUES check box and click OK. Delete the column containing your formulas. -- Kevin Backmann "Adam" wrote: I copy data into excel from an accounts programme, but all the minus signs are at the back so Excel won't add these numbers up i.e 4 3- Th answer comes to 4 rather than one , what can I do about this? Thanks for your help Adam I know you guys/girls know an answer |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
- signs at back of number so won't add up, please help!
Here's one possible solution.
Insert a new column and use the following formula: =IF(RIGHT(A1,1)="-",VALUE("-"&SUBSTITUTE(A1,"-","")),A1) This will move the - sign to the front of all values if they have it, otherwise leave the value as is. You can then perform your SUM on this new column, or "COPY" and "PASTE SPECIAL - VALUES" over your original data. Then delete the new column. HTH, Elkar "Adam" wrote: I copy data into excel from an accounts programme, but all the minus signs are at the back so Excel won't add these numbers up i.e 4 3- Th answer comes to 4 rather than one , what can I do about this? Thanks for your help Adam I know you guys/girls know an answer |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
- signs at back of number so won't add up, please help!
hi! select the range of your data! goto menu bar Data Text to Columns Select Fixed width Select General in Data Format finish you'll get the desired result! -via135 Elkar Wrote: Here's one possible solution. Insert a new column and use the following formula: =IF(RIGHT(A1,1)="-",VALUE("-"&SUBSTITUTE(A1,"-","")),A1) This will move the - sign to the front of all values if they have it, otherwise leave the value as is. You can then perform your SUM on this new column, or "COPY" and "PASTE SPECIAL - VALUES" over your original data. Then delete the new column. HTH, Elkar "Adam" wrote: I copy data into excel from an accounts programme, but all the minus signs are at the back so Excel won't add these numbers up i.e 4 3- Th answer comes to 4 rather than one , what can I do about this? Thanks for your help Adam I know you guys/girls know an answer -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=515835 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
- signs at back of number so won't add up, please help!
Dana DeLouis posted this:
Sub TrailingMinus() ' = = = = = = = = = = = = = = = = ' Use of CDbl suggested by Peter Surcouf ' Program by Dana DeLouis, ' = = = = = = = = = = = = = = = = Dim rng As Range Dim bigrng As Range On Error Resume Next Set bigrng = Cells.SpecialCells(xlConstants, xlTextValues).Cells If bigrng Is Nothing Then Exit Sub For Each rng In bigrng.Cells rng = CDbl(rng) Next End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Adam wrote: I copy data into excel from an accounts programme, but all the minus signs are at the back so Excel won't add these numbers up i.e 4 3- Th answer comes to 4 rather than one , what can I do about this? Thanks for your help Adam I know you guys/girls know an answer -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
- signs at back of number so won't add up, please help!
Select the column with the values having the minus sign following the number,
and run this code........ Sub ReverseMinus() Dim rng As Range, cell As Range Set rng = Selection For Each cell In rng If Right(cell, 1) = "-" Then cell.Value = "-" & Mid(cell, Len(cell) - 1, 99) End If If Right(cell, 1) = "-" Then cell.Value = Mid(cell, 1, Len(cell) - 1) End If Next End Sub Vaya con Dios, Chuck, CABGx3 "Adam" wrote: I copy data into excel from an accounts programme, but all the minus signs are at the back so Excel won't add these numbers up i.e 4 3- Th answer comes to 4 rather than one , what can I do about this? Thanks for your help Adam I know you guys/girls know an answer |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
- signs at back of number so won't add up, please help!
Thank you everybody
"Dave Peterson" wrote: Dana DeLouis posted this: Sub TrailingMinus() ' = = = = = = = = = = = = = = = = ' Use of CDbl suggested by Peter Surcouf ' Program by Dana DeLouis, ' = = = = = = = = = = = = = = = = Dim rng As Range Dim bigrng As Range On Error Resume Next Set bigrng = Cells.SpecialCells(xlConstants, xlTextValues).Cells If bigrng Is Nothing Then Exit Sub For Each rng In bigrng.Cells rng = CDbl(rng) Next End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Adam wrote: I copy data into excel from an accounts programme, but all the minus signs are at the back so Excel won't add these numbers up i.e 4 3- Th answer comes to 4 rather than one , what can I do about this? Thanks for your help Adam I know you guys/girls know an answer -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup with more than number to be retrieved | Excel Worksheet Functions | |||
Preceding a number by zeros, that is still a number | Excel Worksheet Functions | |||
Number of labels on X-axis one more than number of values on Y-axi | Charts and Charting in Excel | |||
Count Number of Characters in a cell? | Excel Discussion (Misc queries) | |||
Need number of Saturdays and number of Sundays between 2 dates | Excel Worksheet Functions |