How to set the value?
 

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

How to set the value?
 

Im not sure of the Indirect way but this will work
=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.",""))))

"Eric" wrote:

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

How to set the value?
 

in an adjacent column/row:

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

then copy-pastespecial as values

How to set the value?
 

replace ";" with ","
sorry

!Q
 

Forgot the value of A1
=REPT("0",4-LEN(A1))&A1&".HK"

"Jarek Kujawa" wrote:

replace ";" with ","
sorry

How to set the value?
 

Why use INDIRECT?
What's wrong with =TEXT(A1,"0000")&".HK"
--
David Biddulph

"Eric" wrote in message
...
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

!Q
 

thks, sorry