text to columns help

[anselyuan]

I have a text and columns problem as below

ID    Description    date

1      abc d            7/12 

        efg                      

2      edskjc s        4/12

        sdffe

Field: 14

3      afsc d            7/12 

        efsg                      

4      edvfva s        4/12

        safe

Field: 15

first of all, I want to split it in to column, there are 2 problems.

1. the description column contains space

2. the description is not in the same row

then, Iwant to add a column call field.

[gc]

What does your data look like after you try to read it into Alteryx with an Input Tool? If I can see that, then I can probably help. Maybe a screen shot of a Browse after the Input?

[anselyuan]

Here is the example. Thanks so much!

[gc]

OK great. Now, can you send an Excel file of what you want that data to look like after doing text-to-columns processing with Alteryx? With that, the workflow can take shape. thanks

[gc]

I'm not sure how to deal with the "Field" rows until I see your desired output, but here's a workflow showing a possible way to deal with the other rows. It can be optimized, but I think it's easier to understand with 1 step per Formula tool.

[anselyuan]

Hi, sorry for delay... Is that possible to make it like result that I uploaded?

[gc]

See if this updated version works. Good luck!

[gc]

Forgot to attach the workflow :P

[Joe_Mako]

The attached is another route that is data driven, based on the logic that character code 160 (0xA0) is the deliminator, and the a : character denotes a field that should be pulled out and and placed with the first ID since the last field record.

[anselyuan]

Could you help me in this case?
DELETE 264750-Creform Pi 30-SEP-2015 STPL 25.00 
DELETE 325452-SERVICE CE 01-JAN-2016 STPL 25.00 

I Just want to make the description like "283750-Creform Pi", "326752-SERVICE CE" a group, and put them in the same column...

Shall I use the same regular expression and replacement?