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

RegEx and rearranging data

tmbauer0512
6 - Meteoroid

I have some data that comes to me formatted like this (these are names):

 

1857;#Last, First;#1108;#Last, First;#306;#Last, First;#2187;#Last, First
262;#Last, First;#3216;#Last, First;#1586;#Last, First;#601;#Last, First
268;#Last, First

 

And I need to re-format it like this:

Last, First;#1857;#Last, First;#1108;#Last, First;#306;#Last, First;#2187

Last, First;#262;#Last, First;#3216;#Last, First;#1586;#Last, First;#601
Last, First;#268

 

I've been playing with RegEx Replace to rearrange each string but I can't get it quite right.  The closest I've gotten is using this expression: (\d+)(;#)(\D+) but it returns something like this:

Last, First;#;#1857Last, First;#;#1108Last, First;#;#306Last, First;#;#2187

 

I'm fairly new to Alteryx so I'm open to constructive criticism and suggestions.

 

 

5 REPLIES 5
Bob_Blackey
11 - Bolide

Hi,

 

Is this coming in all on one line or are there usually four per line?

 

thanks,
Bob

 

gc
9 - Comet

What about a non regex approach, assuming I've understood how the data comes in and needs to go out. Maybe start with this and improve if needed. Personally, I find regex useful when all else fails :)

tmbauer0512
6 - Meteoroid

The number of names varies from line to line.

Bob_Blackey
11 - Bolide

Hi,

 

For some reason I couldn't download GC's workflow so I'll just add mine.

 

The pattern of where #'s show up didn't seem clear so you may have to tweak it.

 

regex_rearrange.png

 

The SELECT is just there to resize the text field in the TEXT INPUT tool.

 

The first REGEX does the real work. It uses the replace function and marked groups () to reorder the data.

 

The second REGEX just replaces a few ## with #.  

 

The FORMULA tool removed the # in the first position.

 

Cheers,

Bob

 

tmbauer0512
6 - Meteoroid

With a little tweaking, this worked perfectly!

 

I hadn't thought of using the RegEx tool twice in a row to replace the multiple characters.  But like I said, I'm an Alteryx newbie so I'm tackling a learning curve.

 

Thank you!!

Labels