Extracting Data to the right or left of a specific character

[edavies]

Hello, 

I was curious if anyone knew how to extract data within a cell that is to the right or left of a character.  For example, "xxxx&xxxx&xxxx."  Is there a way I'd be able to extract the first, second, and third set of "x's" into different columns so you see "xxxx" in three different columns?  Preferably, I'd like to see these 3 sets into different rows, but I believe I can use the Transpose function to get that completed once I know how to extract the "x's" into different columns.  To clarify, I'd also like to ensure that the "&" is removed in the extracted data.

Perhaps this can be done with the RIGHT or LEFT function, but I'm not sure how to build out that formula.

[binuacs · Accepted solution]

@edavies Use the Text-to-column tool to parse the records as & as the delimiter

The splits into column option split the data into columns and the split into rows option will split into rows

[Qiu]

@edavies 
It will be better if you can provide a set of sample data and desired output. 😁

[edavies]

Thank you!  This was very simple and helpful haha.

[edavies]

@binuacs  Would you know if there is a way to create a unique ID for the Row of Data that I am splitting into multiple rows using the Text to Columns tool?

Let me know if this makes sense:

I'd want a unique ID for each row that is split into multiple rows.   Appreciate the help!

[binuacs]

@edavies 

1. add a tile tool after the text to column tool then 2. select unique value from the tile method,

3. select the field name,

4. select leave unsorted

Run the workflow 

the Tile_Num value will be what you are looking for