Find digits with custom format and make them negative

Hii freinds after long time i.e.(after 2 days ),

I want to find digit with custom format i.e ended with Cr and want to make them negative.
Can it be done with single click....

I guess you will have to use a Formula to Add relevant sing (+,-) based on Dr or Cr. And then use Format Cells to add Dr. Cr. at the end..

To the best of my knowledge, I do not know a way to do it directly..

Note: I thought Dr. & Cr. are Text Strings and hence suggested this.. Since Dr & Cr as used as Number Formats, this wont work..

=if(right(a12,2)="Cr",-LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW($1:$10000)))),a12)

Please Login or Register  to view this content.

Did not read the above part, so the formula won't work.

Or maybe something like this?

Please Login or Register  to view this content.

Some error "You have entered too many arguments for this function"

Not unless you use VBA or use the GET.CELL property within Excel.

Using GET.CELL, you need to create a named range (for example CellText) and define the name as =GET.CELL(53,INDIRECT("RC1",FALSE))
Then, on the same row as those numbers (e.g. C12), type in the formula =IF(RIGHT(CellText,2)="Cr",-1,1) * A12
See attached example below (you'll need to enable macros)

Tell us if you'd prefer the VBA option (EDIT: or see NeedForExcel's comment)

Thats great Needforexcel.... awesome..
Please explain the coding...

Thanks quekbc...
I am always hungry for VBA ....

Originally Posted by mangesh.mehendale

Thats great Needforexcel.... awesome..
Please explain the coding...

We basically need VBA as Dr & Cr Values are Set as Number Formats and not Strings.

So the Code basically goes through each Cell and Checks the number format to see if it is a Dr entry or a Cr Entry..

If it is Cr, and already does not have a -ve sign, it multiplies the Cell Value by -1 to make it negative..

Hope it helps..

Thank you for the rep & feedback..

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Thank you everybody...
Thank oeldere .. but still not working not showing negative value...

Last question before I mark it as solve, let me know about GET.CELL property..

Thank... interesting quekbc
...