Conditional Formatting

[dk5209]

Tally (accounting software) data dumped in excel. I want to convert 12345.00Cr into a negative no. and 54321.00Dr into a positive no. automatically. How to do that?

 

[mikerickson]

Use a helper column with the formula
=LEFT(A1, LEN(A1)-2)*IF(RIGHT(A1,2)="Cr",-1,1)

 

[Rick Rothstein]

Select the column with your Cr/Dr values in it, then press Ctrl+H to bring up the Replace dialog box... put Cr in the "Find what" field, put - (a minus sign) in the "Replace with" field and click the "Replace All" button. Then, with the Replace dialog box still up, put Dr in the "Find what" field and remove the entry in the "Replace with" field (that is, this field should be empty) and click the "Replace All" button again. You can now close the dialog box. With the column still selected, press ALT+DE to bring up the "Text To Columns" dialog box and click the Finish button as soon as the dialog box appears. Your values should now be normal Excel numbers.

 

[dk5209]

Thanks Rick. The Data format shows ""0.00" Cr" but the cell shows plain no. How to overcome the problem? Regards.

 

[dk5209]

Use a helper column with the formula
=LEFT(A1, LEN(A1)-2)*IF(RIGHT(A1,2)="Cr",-1,1)

Thanks Mike. I could not understand your cell logic. Pl explain me. Regards.

 

[Rick Rothstein]

Thanks Rick. The Data format shows ""0.00" Cr" but the cell shows plain no. How to overcome the problem? Regards.

Do you have a value of '123Cr' in the cell or maybe '123 Cr' (without the apostrophes) or do you have 123 with a Cell Format of '0.00 Cr'? The fact that this was a data dump would seem to indicat one of the former. If it is the former, the do you have the space in front of the Cr that you are now showing (your original post did not show a space). If so, put a space in front of the Cr in the "Find what" fields (same for Dr) and follow the rest of what I wrote in my original message. Also, are you trying to change the dumped data directly (what my procedure does) or repeat the converted numbers in a different column (what Mike's procedure does)?

 

[dk5209]

Do you have a value of '123Cr' in the cell or maybe '123 Cr' (without the apostrophes) or do you have 123 with a Cell Format of '0.00 Cr'? The fact that this was a data dump would seem to indicat one of the former. If it is the former, the do you have the space in front of the Cr that you are now showing (your original post did not show a space). If so, put a space in front of the Cr in the "Find what" fields (same for Dr) and follow the rest of what I wrote in my original message. Also, are you trying to change the dumped data directly (what my procedure does) or repeat the converted numbers in a different column (what Mike's procedure does)?

Thanks Rick. Is there a way to send you the excel file to see for yourself. Pl let me know.

 

[Rick Rothstein]

Thanks Rick. Is there a way to send you the excel file to see for yourself. Pl let me know.

My email address...

rickDOTnewsATverizonDOTnet

Replace the upper case letters with the symbols they spell out.

 

[Rick Rothstein]

I received a copy of the OP's file and here is how I replied to him...

Okay, the problem was whoever sends you this file did something wrong (in my opinion) and that is causing your problem. Before making the file available to you, they apparently run a macro (I’d hate to think they do it manually) to change the Cell Formatting for each cell in Columns B and E individually. For positive values, the change the Cell Formatting to ""0.00" Dr" and for negative values they change the Cell Formatting to ""0.00" Cr" and then make all the negative values positive leaving you with no negative values remaining at all! What they should have done is use the same Cell Formatting pattern for all numbers, namely this pattern...

 [<0]0.00" Cr";[>0]0.0" Dr";0.00

exactly as I show it. If they had done that, all your values would have retained their positive and negative-ness, but would have displayed exactly as the file you sent me does. That way you would have been able to get rid of the Cr and Dr tags by simply changing the Cell Format to Number with 2 decimal places. But since that is not what they did, you will need a macro to straighten things out for you...

Sub FixCrDrCells()
  Dim Cell As Range
  For Each Cell In Intersect(Range("B:B,E:E"), ActiveSheet.UsedRange)
    If Right(Cell.Text, 2) = "Cr" Then Cell.Value = -Cell.Value
  Next
  Intersect(Range("B:B,E:E"), ActiveSheet.UsedRange).NumberFormat = "0.00"
End Sub

To install the macro, press ALT+F11 to bring up the VB editor and, once it appears, click Insert/Module from its menu bar, then copy/paste the above code into the code window that just opened up. Since this is only going to be done once per file, simply F5 to run the code (give it a moment or two to finish). You can now go back to your worksheet and everything should be straightened out as you wanted.

 

[dk5209]

Rick! many many thanks. It works. Great!!!