Thread: - 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

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

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

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

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

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

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

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
>