Automated Text to Columns

alaska1 Although your options are limited with Excel for the Mac, it doesn't have to be allt that time consuming. Before you do Text-to-columns (TTC), you need ot clean-up the raw data a bit. So open the file and leave everything as is in one single column. Save the file as a regular xlsx file.

1. Replace "- $" with "-"

2. Replace "$" with nothing

This get rid of the dollar signs. Use Ctrl-H, Find & Replace, but do not include the quote marks!

Now do TTC with space as the delimiter. This should produce three columns (columns C,D and E). An amount and the type of fee split in separate words. Merge the two description columns together (column F). Now add a lookup-table that will enable you to add an item number for each fee description (column G) and one more to define which rows belong together (column H).

The resulting table can be pivoted and each group of fees will be transposed into a single row. True, it's still a bit of manual work but it isn't all that bad. And if your data set grows much bigger, it won't take more time.

File attached.