Text to Column to remove spacing between the string

[SH_94]

Hi Community,

I had went through some of the previous discussion and noted that my wanted result a bit different with them. Kindly refer to the screenshot below for your reference.The screenshot below are after i input the txt file and all the words and numbers become one sentence with spacing in between the words

Criteria:

1.May i know how can i make to split into column between the spacing ?

Question

1. After we spilt it into column , some important information are not in the same column anymore, may i know how can we adjust from there? Can anyone share the workflow on this solutions？

Thank you

[phottovy]

Hi @SH_94 

Have you tried using the "Data Cleansing" tool using the "Tabs, Line Breaks, and Duplicate Whitespace" option? This will remove the extra spaces in your data. Then you can use the "Text to Columns" tool and split your data on spaces. Hopefully this will keep everything in the correct columns.

[SH_94]

Hi @phottovy ,

Yes, but the result will remove the spacing in between the words and show the result as below which is not the one that l looking for.

Original Data: AA     BB

                       A   CC

Result : AABB

             ACC

But i want to split into words or number into respective column

[phottovy · Accepted solution]

@SH_94Do you have "All whitespace" selected in the data cleansing too? If so, uncheck that option. The duplicate whitespace option should leave one space but the all whitespace will remove all spaces. Take a look at the attached example to see how I set up the data cleansing tool.

[SH_94]

Hi @phottovy 

Thanks a lot for the workflow provided. It works on certain file uploaded but it does not work for certain file. 

Below is the file that does not work in this case. Could you please help me to identify the solution for this?

[phottovy]

@SH_94 

This turned out to be a little trickier than I first thought but it was a fun challenge to solve. For this .txt file, the original solution won't work because the consecutive spaces actually show where the columns should split. The other trick is you have to use the row with a bunch of dashes to determine the number of characters per column:

---------  --------------------  ----------  ---------------
These dashes show the first column is 9 characters, the second is 20, etc. The double spaces are where you want the split to be.

In the original file, the column headings show up on multiple rows as well. The updated attached work flow accounts for this too.

In the .txt file, there are actually two tables, one for Company, Receipt Currency, and Apply Date but this solution ignores this table and focuses on the larger table.

FYI, the solution involves some RegEx so it could be difficult to modify depending on your comfort level with RegEx. Hopefully this helps you parse more text files as you come across them!