Hello Community,
Could someone assist me? I'm attempting to generate three separate columns from the provided examples. I've tried using the "text to column tool," but it's not effective due to the inconsistent data. Is there an alternative method I can use to achieve this?
example Street name street type unit
CLEVELAND AVE #1 CLEVELAND AVE #1
DAREN DR DAREN DR
GLEN MARY DR #14 GLEN MARY DR #14
SLIBERTY AVE 1F#A SLIBERTY AVE 1F#A
SLIBERTY AVE NS SLIBERTY AVE NS
PALMER HILL RD PALMER HILL RD
NNANTICOKE 2F NNANTICOKE 2F
You'll likely have to use RegEx because of the inconsistent pattern - however what is the logic split into the three columns? For example, it would be hard to distinguish splitting "SLIBERTY AVE NS" into 3 columns versus "PALMER HILL RD" into 2 columns without some form of logic
@alexnajm The logic behind is the first column is Street name the second column is street type and the last column is unit apt.
If i can get first two columns I will be good.
@EvansM understood, but how would Alteryx determine that NS is a unit apartment and RD is not? Normally I would do something to say if the last set of characters contains a number, parse that as a third column... but this can't work with "SLIBERTY AVE NS" because there is no numeric value.
What I'd suggest is creating a list of all of the possible street types (RD, Road, AVE, Drive, DR, etc.) so Alteryx can distinguish those versus whatever unit apt values could come through.