Apr 26 2023 12:01 PM
Apr 26 2023 12:24 PM - edited Apr 29 2023 06:43 AMSolution
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))
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.
Apr 30 2023 09:03 AM - edited May 02 2023 11:53 PM
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".
May 22 2023 09:44 AM
Thanks. The strings are far too long for the formula that I posted earlier.
When I open the second file (the filtered one) in Excel, the comma-separated values are in column B.
The following formula in an empty column in row 2 will return at most 6 comma-separated values:
=IFERROR(LEFT(B2, FIND("|", SUBSTITUTE( B2, ",", "|", 7))-1), B2&"")
This can be filled down.
See the attached workbook.