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

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

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