format text and number

[somusas]

hi all ,

can some body can help me on this,


a1 contains = r12

b1 i want it as = 000r12

is there any way to do this.

[NickyC]

does this formula work for you (in cell B1):

Please Login or Register  to view this content.

[Fotis1991]

Hi

..Where will you use the 000r12 result??

Because a simple way to do this, is to use a helper column.

A1=r12 >> C1(helper & hide column)>>(Texr Format)>>000. Then in B1=C1&A1

Hope to helps you.

[somusas]

sorry i di not give the clear information in a coloumn their are diffrent varities

like

a1: r12
a2 :123h
a3:125a

but all i want in 6 digits

[NickyC]

how about

Please Login or Register  to view this content.

[Fotis1991]

..Then try this:

=IF(LEN(A2)=3;"000"&A2;IF(LEN(A2)=4;"00"&A2;IF(LEN(A2)=2;"0000"&A2;IF(LEN(A2)=5;"0"&A2;""))))

Is it ok for you?

[somusas]

hi all ,

thank you so much

i have found a simple way, it might be useful to you also some times
=RIGHT("000000"&a1,6)

[NickyC]

neat!

Do you ever have values with more than 6 characters - if so, how do you want them treated? Your formula will give the right six characters, mine would give the whole of A1