| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to set the value?
in an adjacent column/row:
=REPT("0";4-LEN(A1))&".HK" then copy-pastespecial as values |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to set the value?
replace ";" with ","
sorry |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
!Q
Forgot the value of A1
=REPT("0",4-LEN(A1))&A1&".HK" "Jarek Kujawa" wrote: replace ";" with "," sorry |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
!Q
thks, sorry
|
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Hybrid Mode
