Comma and separate in excel using function

[acharsanthosh]

I have data '1234 to 56478' and wanted add comma only for values using excel function and data should look like '1,234 to 55,678'
How do I do this?

 

[Peter_SSs]

Can you not just format the cells to include the thousands separator?

 

[acharsanthosh]

No, I have bunch of data and I have to use functions to add comma for every thousands

 

[acharsanthosh]

For the first row the formula ''=LEFT(A1,FIND(" ",A1,1)-4) & ","&MID(A1,FIND(" ",A1,1)-3, LEN(A1) - FIND(" ",A1,1)+1) & ","&RIGHT(A1,3) " is working fine, but not able to do it for rest of 4 since the data is quite different

 

[Peter_SSs]

Hmm, you do have quite a variety to deal with. This appears to cover the cases you have given. I hope there aren't other variations you haven't mentioned.

20 04 01.xlsm
	D	E
1	1234 to 56478	1,234 to 56,478
2	0 to 2450000	0 to 2,450,000
3	1360000+	1,360,000+
4	1234 to 56478	1,234 to 56,478
Format

Cell Formulas
Range	Formula
E1:E4	E1	=TEXT(LEFT(D1,FIND(" ",SUBSTITUTE(D1,"+"," "))-1)+0,"#,##0")&IF(ISNUMBER(FIND(" ",D1)),TEXT(RIGHT(SUBSTITUTE(D1," ",REPT(" ",20)),20)," to #,##0"),"+")