- signs at back of number so won't add up, please help!

#1 02-23-2006, 11:30 AM

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 02-23-2006, 12:25 PM

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 02-23-2006, 12:30 PM

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 · #4 02-23-2006, 12:40 PM

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

Quote:

Originally Posted by Elkar

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

#5 02-23-2006, 01:10 PM

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

#6 02-23-2006, 01:10 PM

Dana DeLouis posted this:

Sub TrailingMinus()
' = = = = = = = = = = = = = = = =
' Use of CDbl suggested by Peter Surcouf
' Program by Dana DeLouis, dana2@msn.com
' = = = = = = = = = = = = = = = =
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

#7 02-24-2006, 04:30 AM

Thank you everybody

"Dave Peterson" wrote:

> Dana DeLouis posted this:
>
>
> Sub TrailingMinus()
> ' = = = = = = = = = = = = = = = =
> ' Use of CDbl suggested by Peter Surcouf
> ' Program by Dana DeLouis, dana2@msn.com
> ' = = = = = = = = = = = = = = = =
> 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
>

Welcome to the Excel Forum

Forum Statistics