• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

formatting problem

P

paramnayak

Member
dear all,
say i have got following things in the cells

cell
a1 20
b1 X
c1 40

In cell d1 i want to display 20 inch X 40 ft

I can use =a1&b1&c1 in cell d1 and it gives 20X40.
Further i can format cell d1 as @ "ft" and it gives 20X40ft.
But is there any way where I can also get the "inch" after 20.

Thanx and rgds
 
dear friends,
thanx a lot.
Is there any way in which if i write 20 X 40 in the cell, the same cell displays 20 inch X 40 ft.
 
@paramnayak

Pardon me for late reply.
Yes this can be done through CF Formula.
=B4="20 X 40"

see the attached sample.

Regards,
 

Attachments

  • CF 20 x 40.xlsx
    8.2 KB · Views: 6
@Khalid NGO

Nice way, but there is 1 problem, if value is say 10 X 10 than it will not work.

@paramnayak

See the file, it uses Worksheet_Change event of VBA to do the same if the same formatting you want say in a column or a range of cells you can specify the same in code. RIght now it is applied to A1:A10.

Regards,
 

Attachments

  • CF 20 x 40.xlsm
    15.2 KB · Views: 4
Hi SM,

Agree with you that vba code is right solution for this situation.

The VBA Macro may need to be tweaked further to check if already inch exists then not to add inch. similarly if ft exists in the cell then not to add it.

The current code will keep adding Inch inch inch or Ft ft ft whenever the value is edited.

Regards,
Prasad DN
 
@prasaddn

I developed the code that OP will update the data in cell once say 1X 1, so it will be formatted, and if he changes it to 10 X 10 it will again be formatted.

Regards,
 
@SM,

when i change the value it was getting formatted again and adding inches / ft again. :(

BTW, I have used your code and tweaked a bit.

Attached is the file.

Regards,
Prasad DN
 
Thanks SM Sir,
Actually i am a novice in vb...
I applied combination of CF and custom formatting to get the result as required.

Your approach is dynamic, thanks for sharing this wonderful idea.

Regards,
 
You must log in or register to reply here.
Back
Top