Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change negative sign from end of number to the beginning of the number.
I need to scan financial reports to a file so that I can then manipulate
columns of figures. I can do this using an OCR program and saving as an Excel file. The reports have the negative sign at the end of the number but Excel requiires it at the begiinning. eg: 1234.56- needs to convert to -1234.56 Is there a way that I can convert a whole column -- Message posted via http://www.officekb.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change negative sign from end of number to the beginning of the nu
Does this work for you:
Sub ConvertNeg() Dim rng As Range Dim oCell As Range Set rng = Range("B1:B100") For Each oCell In rng If Right(oCell, 1) = "-" Then oCell = Left(oCell, Len(oCell) - 1) * -1 End If Next Set rng = Nothing End Sub "GORDON SCALES via OfficeKB.com" wrote: I need to scan financial reports to a file so that I can then manipulate columns of figures. I can do this using an OCR program and saving as an Excel file. The reports have the negative sign at the end of the number but Excel requiires it at the begiinning. eg: 1234.56- needs to convert to -1234.56 Is there a way that I can convert a whole column -- Message posted via http://www.officekb.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change negative sign from end of number to the beginning of the number.
Gordon,
Try this, you will have to change the sheet and range to suit your data. Sub ChangeMinus() 'changes the minus sing on the right of the number 'which is actually shown as text to the left 'so that it becomes a negative number Dim checkRange As Range Dim Cell As Range Set checkRange = Sheets(2).Range("D:I") For Each Cell In checkRange If Right(Cell.Value, 1) = "-" And Left(Cell.Value, 1) < "-" Then Cell.Value = "-" & Application.WorksheetFunction.Substitute(Cell.Valu e, "-", " ") End If Next End Sub Regards Neil "GORDON SCALES via OfficeKB.com" wrote in message ... I need to scan financial reports to a file so that I can then manipulate columns of figures. I can do this using an OCR program and saving as an Excel file. The reports have the negative sign at the end of the number but Excel requiires it at the begiinning. eg: 1234.56- needs to convert to -1234.56 Is there a way that I can convert a whole column -- Message posted via http://www.officekb.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change negative sign from end of number to the beginning of the nu
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change positive number to negative number | Excel Worksheet Functions | |||
How do I change the sign of a base number for use in XIRR? | Excel Discussion (Misc queries) | |||
change negative sign from end of the number to the begining | Excel Discussion (Misc queries) | |||
Change Number to a negative in VBA | Excel Discussion (Misc queries) | |||
2003= negative number&2004= negative number How Do I Calculate gro | Excel Worksheet Functions |