Unable to Deliminate using Text to Column twice

[Goran]

Hi everyone,

I have a column called Item.  In this column there are text which have no leading spaces, have 6 leading spaces, and have 12 leading spaces.

What I'm trying to do is separate these three types of text into 3 columns.  Using Text-to-column I've been able to do it for the 6 leading spaces, but when I place text-to-column again to delimate the ones with 12 leading spaces it just delimates using 6 again.  Any idea what I might be doing wrong?

[echuong1]

You can use regexcountmatches() to count the number of spaces in the field. From there, you can pivot the data to get the number of spaces as the header.

To add in the original values, I added a record ID and then used a join on the Record ID.

[pedrodrfaria]

Hi @Goran 

Please see a pretty simple solution for your question. I'm using the regex to count up to 6 spaces and add a |, then in the text to columns i'm separating using the | as a delimiter.

Pedro.

[Goran]

Thank you kindly for your suggestions.  Oddly, it seems the blank spaces that are in the file are not being read as blank spaces in Alteryx.  So when I try to find \s it says there are no spaces in the column.

[pedrodrfaria]

@Goran can you provide a sample? It should be much easier.

[Goran]

Here's a small sample.  As you can see, some text in the Item column have no leading spaces, some have 6 and others have 12.  These should all be put into their own columns.  Further, if I could get the preceeding row that has less spaces to copy down in the same column that'd be amazing.  So for example:

Current

Work In

      2.1.1

            2.1.2

Output

Work In

Work In   2.1.1

Work In   2.1.1   2.1.2

[Goran]

Bump.  Any thoughts on this one?