grabbing text between odd punctuation

[parria1]

I have a field called filename that alteryx created when i used the Read all Excel files macro to read files from a directory. below is an example of that field.

C:\Users\ME\Desktop\Group\Appledore Physicians NH.xlsx|||`Appledore Phys NH$`

I am trying to parse out Appledore Phys NH and cannot figure it out. I have tried all the RegEX Parse formulas to get text between commas and other punctuation such as brackets and just replace the punctuation in the example with the ` symbol but i cannot get anything to work.

alternatively i could just grab the sheet name from  each Excel file and use that to create a list name field but not sure how to go about that either.

[bpatel]

hi @parria1 ,

what if you tried something like this? using the text to columns to parse out sheet names and data cleansing to remove punctuation 

hope this helps!

[AbhilashR]

Hi @parria1, you could use a combination of functions within the Formula tool to strip out the specific text you are looking for. 

TrimRight(
  TrimLeft(
    Right([Field1],
        Length([Field1])-STRCSPN([Field1],'|||')
        )
  ,"|||'")
,"$'")

In the formula above, I use STRCPN function to identify the position from which I want to strip the sheet name using the identified |||. From there using a combination of Right and Trim function I arrive at the final result. Attached is a sample implementation for you to build upon.

I hope this helps!

[DavidP · Accepted solution]

The problem with the regex is the `   ` around the sheet name. They look very similar to ' ', but they're different.

So this regex correctly parses the sheet name

.+\|\|\|`(.+)\$