Automated Text to Columns

Occasional Visitor

I download data from a software program on a weekly basis to use for reconciliation. The way the data is exported however is hard to use, as its all in one column and the data is not separated by commas. I attached a sample of the data exported, and on the right an example of how I eventually need it to be formatted. Currently it takes a long time every week to format as I need, and I'm wondering if it's possible to automate this process and simply paste in the new data weekly.

 

I use excel version 16.41 for Mac.

 

Thanks in advance!

1 Reply

@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.

Screenshot 2021-12-10 at 06.20.19.png

File attached.