Data Pursing

[EvansM]

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

[alexnajm]

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

[EvansM]

@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.

[alexnajm]

@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.