| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
separator of thousands
hi all,
my (part) formula = VLOOKUP(A1,A3:B18,2) This will give me (say for example) 1234567. i like this number to look like 1,234,567 and here what i did =IF(LEN(VLOOKUP(A1,A3:B18,2))=7,REPLACE(REPLACE(VL OOKUP(A1,A3:B18,2),2,0,","),6,0,","). Is there easier way to do that ? Because i have to repeat this process many times for if (len(A1,A3:B18,2))=4 then =5 and so on up to =12 which i end up with a too large unfinished formula thanks for any help |
|
#2
|
|||
|
|||
|
Hi
one way: Why not use 'Format - cells' for this? If you need a formula (but remember the result then is a STRING value) try: =TEXT(VLOOKUP(A1,A3:B18,2),"#,##0") "excelFan" wrote: hi all, my (part) formula = VLOOKUP(A1,A3:B18,2) This will give me (say for example) 1234567. i like this number to look like 1,234,567 and here what i did =IF(LEN(VLOOKUP(A1,A3:B18,2))=7,REPLACE(REPLACE(VL OOKUP(A1,A3:B18,2),2,0,","),6,0,","). Is there easier way to do that ? Because i have to repeat this process many times for if (len(A1,A3:B18,2))=4 then =5 and so on up to =12 which i end up with a too large unfinished formula thanks for any help |
|
#3
|
|||
|
|||
|
Thanks Frank for your help
=text(vlookup(A1,A3:B18,2),"#,##0) works very fine, thanks again "Frank Kabel" wrote: Hi one way: Why not use 'Format - cells' for this? If you need a formula (but remember the result then is a STRING value) try: =TEXT(VLOOKUP(A1,A3:B18,2),"#,##0") "excelFan" wrote: hi all, my (part) formula = VLOOKUP(A1,A3:B18,2) This will give me (say for example) 1234567. i like this number to look like 1,234,567 and here what i did =IF(LEN(VLOOKUP(A1,A3:B18,2))=7,REPLACE(REPLACE(VL OOKUP(A1,A3:B18,2),2,0,","),6,0,","). Is there easier way to do that ? Because i have to repeat this process many times for if (len(A1,A3:B18,2))=4 then =5 and so on up to =12 which i end up with a too large unfinished formula thanks for any help |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
