Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting 123 CR & 4567 DB to 123 & -4567
My financial report with several columns and all the rows displays numeric
values as text with DB and CR that I need to convert to positive and negative numerics. The following function does the job, but is there a macro available that would do it? =IF(RIGHT(E12,3)=" cr",1*LEFT(E12,LEN(E12)-3),-1*LEFT(E12,LEN(E12)-3)) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting 123 CR & 4567 DB to 123 & -4567
Select the cells you want to convert and run:
Sub FixThem() For Each r In Selection v = r.Value vn = Left(v, Len(v) - 3) If Right(v, 2) = "DB" Then r.Value = -vn Else r.Value = vn End If Next End Sub -- Gary''s Student - gsnu200909 "bud i" wrote: My financial report with several columns and all the rows displays numeric values as text with DB and CR that I need to convert to positive and negative numerics. The following function does the job, but is there a macro available that would do it? =IF(RIGHT(E12,3)=" cr",1*LEFT(E12,LEN(E12)-3),-1*LEFT(E12,LEN(E12)-3)) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting 123 CR & 4567 DB to 123 & -4567
You didn't say which columns, so I just used A, C:E and H in my example,
just change the Range assignment in the Set statement to the columns that have your credit/debit values in this macro... Sub FixCreditDebitCells() Dim R As Range, C As Range Set R = Range("A:A,C:E,H:H") R.Replace " cr", "", MatchCase:=False R.Replace " db", "-", MatchCase:=False For Each C In R.Columns C.TextToColumns C, TrailingMinusNumbers:=True Next End Sub -- Rick (MVP - Excel) "bud i" <bud wrote in message ... My financial report with several columns and all the rows displays numeric values as text with DB and CR that I need to convert to positive and negative numerics. The following function does the job, but is there a macro available that would do it? =IF(RIGHT(E12,3)=" cr",1*LEFT(E12,LEN(E12)-3),-1*LEFT(E12,LEN(E12)-3)) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting 123 CR & 4567 DB to 123 & -4567
The code I posted *could* affect any headers the specified columns **if and
only if** they contain the letters <spacecr or <spacedb in them (they would get replaced). If you have such a situation, then use this code in place of the code I posted originally, or simply use it instead of my originally posted code (it will be as speedy as my originally posted code was)... Sub FixCreditDebitCells() Dim R As Range, C As Range Set R = Intersect(Range("A:A,C:E,H:H"), ActiveSheet.UsedRange).Offset(1) R.Replace " cr", "", MatchCase:=False R.Replace " db", "-", MatchCase:=False For Each C In R.Columns C.TextToColumns C, TrailingMinusNumbers:=True Next End Sub If you have two (or more) header rows, then change the value in the Offset property call to that number of rows. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... You didn't say which columns, so I just used A, C:E and H in my example, just change the Range assignment in the Set statement to the columns that have your credit/debit values in this macro... Sub FixCreditDebitCells() Dim R As Range, C As Range Set R = Range("A:A,C:E,H:H") R.Replace " cr", "", MatchCase:=False R.Replace " db", "-", MatchCase:=False For Each C In R.Columns C.TextToColumns C, TrailingMinusNumbers:=True Next End Sub -- Rick (MVP - Excel) "bud i" <bud wrote in message ... My financial report with several columns and all the rows displays numeric values as text with DB and CR that I need to convert to positive and negative numerics. The following function does the job, but is there a macro available that would do it? =IF(RIGHT(E12,3)=" cr",1*LEFT(E12,LEN(E12)-3),-1*LEFT(E12,LEN(E12)-3)) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Converting ASC to csv | Excel Discussion (Misc queries) | |||
Converting to PDF | Excel Discussion (Misc queries) | |||
converting | Excel Discussion (Misc queries) | |||
Converting a date to a text field w/o converting it to a julian da | Excel Worksheet Functions | |||
Converting .xls to .doc | Excel Discussion (Misc queries) |