extract specific characters in the middle of a string

[mcas1]

Hello community,

I need to separate text from a string in a single column that has two cases as follows:

Case 1: ABC/DE01FG02  10/19/2019 Output required: DE01FG02

Case 2: ABC/XY01 02/19/2019  Output required: XY01

Both cases appear in one column of data, and i would like to achieve both outputs in a separate column, if possible.

Thanks in advance for the help, it is much appreciated!

[MarqueeCrew]

@mcas1 ,

 please try using a text to columns tool and use the slash / as your delimiter.  You will get 2 columns. 

cheers,

 mark

[Harbinger]

the most elegant solution will be a simple regex to parse text between the forward slash and the space.

(?<=\/)(.*?)(?=\s)

I have attached this example!

[Qiu]

@mcas1 

See if this is what you need.

[mcas1 · Accepted solution]

@Harbinger, 

Thank you so much. This worked!

The string has two slashes so it actually looks more like this: AB/CD/EF01CD01 10/29/2019

When I use your expression, it's splitting up the string from the first slash, when i actually want it to split from the second? Is this possible?

If so, can you show me what the revised expression is? And also, i'm new to this... what does that expression actually mean if i want to do this again in the future? 

[Harbinger · Accepted solution]

@mcas1 oh that pesky second match! 

Try this bad boy: ^(?:[^\/]*\/){2}([^\/ ]*). 

Regex is a science unto itself and, for me at least, it takes a decent amount of trial and error. I use an online tool called Regexr (https://regexr.com/) that allows me to paste the sample text and then develop my regex and see how it matches in real time. 

Here is the explanation of the expression (taken from regexr!) 

I added the updated workflow! 

[mcas1]

@Harbinger,

Thanks so much for your help with this, the updated workflow is great. Time to get comfortable with Regex.... 🙂

[Harbinger · Accepted solution]

It’s a powerful tool to have in your tool box, for sure!