RegEx Parsing Question

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

Regex

Parse

Accepted answers

Claje

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!

Claje_TextToColumns.yxzp

All comments

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.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703