I need to clean a column of data which contains the percentage coverage of different materials. I would like to split the columns into e.g. '30% water | 20% grass | 20% coarse', so that each percentage of a material is in a different column.
Is there a formula for achieving this? Possibly by adding spaces between the % and following words first?
I managed it without difficult formula.
Just type some examples like i did in column C (2 or 3 examples) then mark all the cells and choose Flash Fill.
Do for each column.
Last column you can clean with this formula:
=IF(ISNUMBER(SEARCH("%";E3));E3;"")
You could use Power Query, it just takes a few seconds...
This you can copy and paste it where you want. Then you can delete the Query if you don't need it anymore, or you can update it when new values are added to your original table.
Here a formula based solution. This is for max. 3 items in a cell. You can make more formulas understanding the example in case you have more than 3 items in a cell.
B4=
=LEFT(A4;MIN(SEARCH({0;1;2;3;4;5;6;7;8;9};A4&"0123456789";3))-1)
C4=
=MID(A4;
MIN(SEARCH({0;1;2;3;4;5;6;7;8;9};A4&"0123456789";3));
MIN(SEARCH({0;1;2;3;4;5;6;7;8;9};A4&"0123456789";MIN(SEARCH({0;1;2;3;4;5;6;7;8;9};A4&"0123456789";3))+3))-MIN(SEARCH({0;1;2;3;4;5;6;7;8;9};A4&"0123456789";3)))
C4=
=MID(A4;
MIN(SEARCH({0;1;2;3;4;5;6;7;8;9};A4&"0123456789";3));
MIN(SEARCH({0;1;2;3;4;5;6;7;8;9};A4&"0123456789";MIN(SEARCH({0;1;2;3;4;5;6;7;8;9};A4&"0123456789";3))+3))-MIN(SEARCH({0;1;2;3;4;5;6;7;8;9};A4&"0123456789";3)))
Formula in C9:
=SUBSTITUTE(SUBSTITUTE(C4;" ";"");"%";"% ")
You can wrap it around the above formulas. I didn't do it, because otherwise it looks to complicated for you.
