How to split a column with percentages and words-CodePudding

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?

CodePudding user response：

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

CodePudding user response：

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.