How to remove everything after 3rd comma in Excel cell

Question

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

Could you include one cell value in the body of the question for testing? — Desert Eagle, Jul 15 '22 at 17:39
Sir, thanks for quick response. I am very new in excel. I just want to remove all links after 3rd comma. I mean I just want to keep 3 links per cell. — Hannah James, Jul 15 '22 at 17:40
Perhaps you just need to adjust the cell reference, i.e. `=LEFT(E2,FIND("|",SUBSTITUTE(E2,",","|",3)))` — Spectral Instance, Jul 15 '22 at 19:01

score 0 · Answer 1 · answered Jul 16 '22 at 06:22

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)

score 0 · Answer 2 · answered Jul 16 '22 at 06:37

0

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]"))

answered Jul 16 '22 at 06:37

Harun24hr

2 Answers2