Text to Columns issue

[rmartinez4]

Hi All,

I tried using text to columns to separate a text data in a cell by a comma delimiter but its not catching all the comas to delimit. Is there away around it? Why is Alteryx missing the commas?

Thank you for the help in advanced.

[ChrisTX]

Can you post the relevant section of your workflow including sample data?

Or at least post the configuration of your Text to Columns tool.

Chris

[rmartinez4]

I have about 73K rows the varies from similar to this address this data 

IP

12545:4edT,000.16.99.000,000.001.000,12545:4edT,000.16.99.000

12545:4edT,000.16.99.000,03.001.000.12,03.001.000.12

12545:4edT,000.16.99.000,000.001.000.1,000.16.99.000

The result should be to split all the data into separate columns but it seems to leave some of the data how looks it below

ip1                              

12545:4edT,0     

12545:4edT,

12545:4edT

[rmartinez4]

I figured it out I was using the all columns but the last column contained the left over since text to column was only splitting into 3 columns. I guess i need to ensure to increase the numbers of columns to avoid the last column containing the left overs.  

[rmartinez4]

Sorry its still happening...

[ChrisTX · Accepted solution]

We can't help without data examples.  When you write "its still happening", please provide examples where it is still happening.

When I use Text to Columns, I set the number of columns to 99, then use the Data Cleansing tool to drop the null columns. 

Chris

[rmartinez4]

I wish to share examples but my data is sensitive to share. I did find some random IP online and I put them together  similar how it looks to my data

. 

Hostname           IP            Date                                                   OS

Test1 192.0. 2.146,2001:0db8:85a3:0000:0000:8a2e:0370:7334,192.0. 2.146,192.168.108.107,2005:0db8:85a3:0000:0000:8a2e:0370:7334                5/5/2022              Windows 98

Test2 2001:0db8:85a3:0000:0000:8a2e:0370:7334,192.168.108.105,2001:0db8:85a3:0000:0000:8a2e:0370:7334,192.0. 2.146,192.168.108.105                5/4/2022              Windows 2005

Test3     172.168.108.105,2001:0db8:85a3:0000:0000:8a2e:0370:7334,192.0. 2.146,192.168.108.105,182.168.108.105                5/3/2022              Windows 95

Test4 172.168.108.105,2001:0db8:85a3:0000:0000:8a2e:0370:7334,192.0. 2.146,192.168.108.105,182.168.108.108,182.158.108.105                5/4/2022              Windows 96

[Robin_McIntosh]

In addition to what @ChrisTX said above about increasing to 99 columns, you could do a split to rows like this when there could be different number of outputs (commas).

[rmartinez4]

Sorry but for some reason I can't post table due html error in this site but if you see the picture of my table i posted that's how my data is setup. splitting into rows scares me because how will I get them get them back in alignment with the other corresponding columns. 

[rmartinez4]

It looks like this seem to do the trick. Can you explain more why this work? I want to take make some notes to avoid in the future.