Forum Discussion

Nostromo's avatar
Nostromo
Copper Contributor
Apr 26, 2023

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?
Excel
  • HansVogelaar's avatar
    HansVogelaar
    Apr 26, 2023

    Nostromo 

    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.

SergeiBaklan's avatar
SergeiBaklan
MVP
Apr 29, 2023

HansVogelaar 

That's misprint in the formula. If value is in F2, it shall be

=TRIM(LEFT(SUBSTITUTE( F2, ",", REPT(" ", 255), 6), 255))

  • Nostromo's avatar
    Nostromo
    Copper Contributor
    Apr 30, 2023

    I cut everything back to one cell - A1, pasted the formula in B1 & the cell populated with data which I pasted as values back into A1, it worked! I then opened the original file & tried it with a full column, worked!

    Thanks for your patience, I've been struggling with this for months. My spreadsheet knowledge is basic & I have no Idea how these formulas work, anyway my motto is "you can't know less by learning more".

Resources