Txt File Cleanup Question

Hello Community,

I have a question for you and I am not even sure it is possible to achieve. I have a txt file that is used to map accounts but the accounts are in a hierarchy based on how many times the name is tabbed in. So some of the columns can have one tab and some can have 5+ that would relate or roll up into the field above it. Here is an example:

But I need it to be in this format so I can use it for my mapping

Let me know if you have any thoughts or ideas. I have attached the sample txt file.

Thanks in advance for thinking through this with me.

Sample.txt

Tips and Tricks

Custom Tools

Transformation

Data Investigation

Accepted answers

Ben_H

Hi @canddig,

Here's my take on it.

This method should work for any number of levels.

Regards,

Ben

TXT File Cleanup.yxzp

All comments

DataNath

About to hop off my laptop but I started having a stab at this. Looks like the raw text has an additional layer of being 'tabbed' which pushes it a column further than you'd like, as I was thinking I could just fill down the values with multi-row formula tools if the current row was null. Have left my workflow in case it comes in handy and helps towards a solution!

Transform into hierarchy.yxmd

gabrielvilella

You can have something like this. This is going to depend on how many layers you have. If you really want to make this dynamic, you can build a macro rather than copying and pasting the multi-row multiple times.