Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete "CR" at the end of a number and make the # negative?
The program I am using is placing a "CR" at the end of every negative
number to denote that it is a "credit". This poses a problem when using the data, as Excel won't recognize the number. I need to get/ write a macro that deletes the "CR" and makes the number negative. I tried the "Left" commands in excel, but it won't work since you have to specify how many numbers numbers are to the left and that changes depending on the size of the number. Any ideas? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete "CR" at the end of a number and make the # negative?
try this
=if(exact(Right(a1,2),"CR"),value(left(a1,len(a1)-2)*-1),a1) On Aug 15, 5:32 pm, wrote: The program I am using is placing a "CR" at the end of every negative number to denote that it is a "credit". This poses a problem when using the data, as Excel won't recognize the number. I need to get/ write a macro that deletes the "CR" and makes the number negative. I tried the "Left" commands in excel, but it won't work since you have to specify how many numbers numbers are to the left and that changes depending on the size of the number. Any ideas? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete "CR" at the end of a number and make the # negative?
You may not really need a macro for this...
Assuming the "numbers" end with " CR"(a space followed by CR) Try this: Select the range to be impacted Then...From the Excel main menu: <edit<replace Find what: CR<-----a space followed by CR Replace with: - Click [Replace All] one more step.... <data<text-to-columns Click [Next] until Step_3_of_3 Check: General (If you click the [Advanced] button, you'll see that Excel's default is to convert numbers with trailing minus signs to negative numbers) Click [Finish] Does that help? *********** Regards, Ron XL2003, WinXP " wrote: The program I am using is placing a "CR" at the end of every negative number to denote that it is a "credit". This poses a problem when using the data, as Excel won't recognize the number. I need to get/ write a macro that deletes the "CR" and makes the number negative. I tried the "Left" commands in excel, but it won't work since you have to specify how many numbers numbers are to the left and that changes depending on the size of the number. Any ideas? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete "CR" at the end of a number and make the # negative?
Tim has given you a method to do it with a spreadsheet formula coupled with
a help column. However, if you need to physically change the values in their current cells, then this macros should do that for you (just set the appropriate range in the For Each statement line).. Sub RemoveCR() Dim R As Range For Each R In Range("A1:A200") If R.Value Like "*[Cc][Rr]" Then R.Value = -Left(R.Value, Len(R.Value) - 2) End If Next End Sub Rick wrote in message ps.com... The program I am using is placing a "CR" at the end of every negative number to denote that it is a "credit". This poses a problem when using the data, as Excel won't recognize the number. I need to get/ write a macro that deletes the "CR" and makes the number negative. I tried the "Left" commands in excel, but it won't work since you have to specify how many numbers numbers are to the left and that changes depending on the size of the number. Any ideas? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete "CR" at the end of a number and make the # negative?
On Aug 15, 4:47 pm, "Rick Rothstein \(MVP - VB\)"
wrote: Tim has given you a method to do it with a spreadsheet formula coupled with a help column. However, if you need to physically change the values in their current cells, then this macros should do that for you (just set the appropriate range in the For Each statement line).. Sub RemoveCR() Dim R As Range For Each R In Range("A1:A200") If R.Value Like "*[Cc][Rr]" Then R.Value = -Left(R.Value, Len(R.Value) - 2) End If Next End Sub Rick wrote in message ps.com... The program I am using is placing a "CR" at the end of every negative number to denote that it is a "credit". This poses a problem when using the data, as Excel won't recognize the number. I need to get/ write a macro that deletes the "CR" and makes the number negative. I tried the "Left" commands in excel, but it won't work since you have to specify how many numbers numbers are to the left and that changes depending on the size of the number. Any ideas?- Hide quoted text - - Show quoted text - I used this, and tried to add the ActieSheet command to apply to multiple worksheets, but its not working for me. I've looked at the code several times. Ideas? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete the number "49" from each cell in a column | Excel Worksheet Functions | |||
make "F51668H200AU" into "F5166-8H200AU" | Excel Discussion (Misc queries) | |||
Make "Edit" mode default, rather than "Enter"? | Excel Discussion (Misc queries) | |||
How to make number of "static" fields stay the same when copying? | Excel Discussion (Misc queries) | |||
how can I make an excel cell "mark" or "unmark" when clicked on? | Excel Discussion (Misc queries) |