How to set the value?

  • Thread starter Thread starter Eric
  • Start date Start date
E

Eric

Does anyone have any suggestions on how to set the value using Indirect
function?
I would like to change the 4- digit + .HK, the maximum digit is 4-digit.

There is a given number 5 in cell A1, it should return 0005.HK. in cell B1
There is a given number 941 cell A1, it should return 0941.HK in cell B1
There is a given number 2388 cell A1, it should return 2388.HK in cell B1

Does anyone have any suggestions on how to do it?
Thanks in advance for any suggestions
Eric
 
Im not sure of the Indirect way but this will wor
=IF(LEN(A1)=1,"000"&TEXT(A1,0)&".HK.",IF(LEN(A1)=2,"00"&TEXT(A1,0)&".HK.",IF(LEN(A1)=3,"0"&TEXT(A1,0)&".HK.",IF(LEN(A1)=4,TEXT(A1,0)&".HK.",""))))
 
in an adjacent column/row:

=REPT("0";4-LEN(A1))&".HK"

then copy->pastespecial as values
 
Back
Top