How to Extract Cell format Type as cell value to another cell

dharmadme

New Member
Joined
Mar 19, 2014
Messages
17
Dear Friends,

I am trying to create Macro Program for my regular activity for Data Extracted from Tally.

I am having cells filled with Amount as number and custom Formatted as ""0.00" Cr" and ""0.00" Dr"

So Cell display will be D5 = 100 (Display as "100 Cr") and D6 = 150 (Display as "150 Dr") like "N" number of rows


I want below result

If D5: "100 Cr" then I need E5 = Cr
If D6 = "150 Dr" then I need E6 = Dr

Example Photo in below link
https://drive.google.com/file/d/0B3NIO-ZYSEYjanp6VUFMOFZrWHc/view?usp=sharing

view
 
Last edited:

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
What defines whether it will be Cr or Dr?
I'm thinking that you can alter the way you format. It might be easier to add a column. So column D has your number data in it, Column E has your Cr or Dr add-on, and Column F would have a formula.
So F5 would be =D5&" "&E5
and F6 would be =D6&" "&E6.

Just a thought. I don't think there's a way to pull letters from your custom format, though. Without knowing more about how you get your data or exactly what you want to do with it, it's difficult to find a work around.
 
Upvote 0
Resolved: Re: How to Extract Cell format Type as cell value to another cell

Thank you for your response.

The data is Exported from Tally.
Tally Formatting the cell as "0.00 Cr" or "0.00 Dr" in D column

So I am Entering Manually in E Column as Dr or Cr

But instead of manual entry I need to get those value automated like Formula or Macro code.

Now I found a solution.
Find and Replace cell Format is helped me to do my requirement. I used that for macro preparation and worked well.




What defines whether it will be Cr or Dr?
I'm thinking that you can alter the way you format. It might be easier to add a column. So column D has your number data in it, Column E has your Cr or Dr add-on, and Column F would have a formula.
So F5 would be =D5&" "&E5
and F6 would be =D6&" "&E6.

Just a thought. I don't think there's a way to pull letters from your custom format, though. Without knowing more about how you get your data or exactly what you want to do with it, it's difficult to find a work around.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,106
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top