How to remove everything after 3rd comma in Excel cell-CodePudding

I have data in excel format

I want to remove all links after 3rd comma in each cell. I used this code

=LEFT(X13,FIND("|",SUBSTITUTE(X13,",","|",3)))

But it is not working. Can someone please help

CodePudding user response：

Tested this but edited to work with cell A2:

So, no error and I separated the substitute() out to see the output of that. cell C3 is the formula used in cell C2 and cell E5 idem for E4.

I like this as I would have used find() to find the first, then second, then third comma:

FIND(",",A2,FIND(",",A2,FIND(",",A2,1) 1) 1)

CodePudding user response：

Try below formula-

=TRIM(LEFT(SUBSTITUTE(A1,",",REPT(" ",5000),3),5000))

If your excel support TEXTJOIN() then can try-

=TEXTJOIN(",",TRUE,FILTERXML("<t><s>"&SUBSTITUTE(A1,",","</s><s>")&"</s></t>","//s[position()<=3]"))