Forum Discussion
Filtering Cells
I have an Excel column with with various amounts of comma separated values, how can I remove all values that exceed 6, so that no more than 6 values remain in each cell?
Let's say your values are in F2 and down.
In G2, enter the formula
=TRIM(LEFT(SUBSTITUTE(G2, ",", REPT(" ", 255), 6), 255))=TRIM(LEFT(SUBSTITUTE(F2, ",", REPT(" ", 255), 6), 255))
Fill down.
If you want to get rid of the original values, copy column G and paste it as Values over column F. You can then remove column G.
Let's say your values are in F2 and down.
In G2, enter the formula
=TRIM(LEFT(SUBSTITUTE(G2, ",", REPT(" ", 255), 6), 255))=TRIM(LEFT(SUBSTITUTE(F2, ",", REPT(" ", 255), 6), 255))
Fill down.
If you want to get rid of the original values, copy column G and paste it as Values over column F. You can then remove column G.
That's misprint in the formula. If value is in F2, it shall be
=TRIM(LEFT(SUBSTITUTE( F2, ",", REPT(" ", 255), 6), 255))
Of course, thanks!
- Thanks for the reply, however when I pasted over, the cells went blank. I'm using Excel online.
