Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Text to column with Fixed width

Valentin_NY
5 - Atom

Hi,

 

I am trying to perform a simple text to column operation but using fixed width instead of delimiters. My column is in the format DDMMMYYYY (e.g. 23JUL1907) and I am trying to extract the year.

 

My understanding is that I should actually be using the regex tool, but I am struggling to understand its syntax.

 

Any help would be greatly appreciated.

 

Many thanks,

Val

7 REPLIES 7
haroon_sa
10 - Fireball

Hi @Valentin_NY

use this expression in Reg ex tool

(\d{4})

Capture.PNG

Thanks,

Haroon

BenMoss
ACE Emeritus
ACE Emeritus
An alternative would be to simply use the formula tool.

Right(field,4)

Or right(tosfrong(field,4))

If you wish too pull out the day and month too then you can use regex in parse mode where you use brackets to pull out the different variables.

(\d\d)([[ALPHA]][[ALPHA]][[ALPHA]])(\d\d\d\d)

(I wrote that blind so it may be slightly off)

Ben
Valentin_NY
5 - Atom

Great, thanks!

alisagoodeair
6 - Meteoroid

I'm trying to do the same thing, but i don't want to leave off any part of the field. So, for example, if one of mine is 20180109, I need to have a separate column for year, month and day.

I've been trying the RegEx tool with parse but I don't know how to construct the expression - and I have looked at multiple other places in the community and if it is answered somewhere I don't see it. 

 

This is what I have, but when I run it, my Month Day and Year fields are Null.

 

2018-12-03_12h37_20.png2018-12-03_12h38_02.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Any thoughts as to how I should proceed?

Thanks!

hydrogurl01
8 - Asteroid

I'm having the same issue. Did you figure out what was wrong?

alisagoodeair
6 - Meteoroid

I did not figure out how to do it. I found a different angle to approach that worked much better. What's your specific issue? My other approach may or may not be helpful to you, but if it is, I'd be happy to help.

RazwanBaber
5 - Atom

You are correctly parsing the three columns you want as an output. You just need to adjust the regex formula inside each set of parentheses. 

 

The expression you're looking for is (\d{4})(\d{2})(\d{2}). That will parse out what you need I think.

 

 

Labels