RegEx Parsing Question

[ckirgiz]

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

[WillBowman]

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

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

[ckirgiz]

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:

Column1	Column2	Column3	Column4
UserID1	UserName1 UserID2	UserName2 UserID3	UserName3 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.

Column1	Column2	Column3	Column4
User ID1 / UserName1	User ID2 / UserName2	User ID3 / UserName3	User 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!

[ckirgiz]

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. 

[WillBowman]

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

[Claje · Accepted solution]

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!

[ckirgiz]

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