community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

RegEx Parsing Question

Meteor

I have a user access report of an IT application. All the users are pulled from the application to a one cell in Excel like below. i could not parse it text to column tool. I believe the answer might be RegEx. Can anyone please help me to figure out how to parse it? (Please also see the wanted result at the end)

 

Thank you so much in advance.

 

 

Source Data in one cell in Excel:

 

UserID1 / UserName1

 

UserID2 / UserName2

 

UserID3 / UserName3

 

UserID4 / UserName4

 

UserID5 / UserName5

 

...

 

UserID300 / UserID300

 

Result Needed (In Two Columns):

 

User ID                    User Name

UserID1                   UserName1

UserID2                   UserName2

.....                           ......

UserID300               UserName300

Asteroid

([^/]*)/([^/]*)

Group 1

Group 2

 

This is saying

match anything except for / as long as long as possible

then match /

then match anything not / for as long as possible

 

 

Why could you not parse with text to columns? It should be / delimiter

 

 

 

EDIT: if the whitespace is always there in the middle ([^/]*)\s/\s([^/]*) will strip it out

Highlighted
Meteor

When I use / delimeter in text to column tool, it separates UserID1 and UserName one into a two different column. So I see something like this when I try / delimeter:

 

Column1Column2Column3Column4
UserID1UserName1 UserID2UserName2 UserID3UserName3 UserID4

 

But I need to see something like that below first, then I can use text to column tool with / delimeter to seperate UserID1 and UserName1 into two different column.

 

Column1Column2Column3Column4
User ID1 / UserName1User ID2 / UserName2User ID3 / UserName3User ID4 / UserName4

 

Hope I was able to explain. I might miss something since I am a beginner. I am going to try thr RegEx formula you provided. Thank you so much!

Meteor

Unfortunately it did not work. I think it did not work because I am not sure we consider the space before and after the / delimiter in this formula. 

Asteroid

Can you post a screenshot of your input? You will probably want to use tokenize on the regex tool. If you post your input i can demo it

Magnetar
Magnetar

Hi,


I've attached an example which does not use RegEx that I think accomplishes what you need.


I believe you need to use two Text to Columns tools for this to work, because of the new lines in your cell of data.

 

The first one splits on \n - this is alteryx's code for new line characters, and splits to rows.


Then, we filter out any empty rows.

 

The next one splits on the "/" character and splits to two columns.


Hope this helps!

Meteor

Thank you so much both for your help. Claje`s solution worked.

Labels