CELL("filename") in alteryx?

[Davidmcho]

Hello I'm in the middle of a workflow, and I need to create a process that replicates the formula 

=INDEX('Clients'!$A:$I,MATCH($AO3&$AV3&MID(CELL("filename",$H3),FIND("]",CELL("filename",$H3))+1,255),'Clients'!$D:$D&'Clients'!$F:$F&'Clients'!$E:$E,0),1)<>"","Yes","No")

I specifically need help in designing the CELL("filename") part of the formula. If someone knows how to create this whole formula altogether that would be awesome, if not, I really need the CELL formula.

Like always, thank you Alteryx Community.

[NicoleJohnson]

Can you provide a little more information about what this formula is trying to achieve? Excel formulas don't always transfer straight into an Alteryx formula or tool, but I imagine we can achieve the same result if we knew more about what your workflow looks like prior to this formula, and what you'd like your end result to be.

As a start, you can bring in your filename as part of your input tool (which I think is part of what you are asking for), which will put the full file path or just the filename as a field in your data. 

Let us know what additional information you can provide about what you are trying to achieve? Thanks!

NJ

[Davidmcho]

Let's say I bring in the full path using "output file name as field", and the full file name is C:\Users\XXX\Desktop\TEST\Alteryx\ALTERYX TEST 2017 Template.xlsx|||`1040$`

How do I cut that to just get the '1040' portion of the file name? 

[jrgo · Accepted solution]

@Davidmcho,

RegEx is typically the best option for string pattern based matching/extraction. Below is a screenshot illustrating the RegEx_Replace() function to extract the text that appears between a pipe and tick character (|`) and dollar sign ($). If you aren't familiar with RegEx, there's a TON of content throughout Community. However, expression should get you want you need.

REGEX_REPLACE([FileName],'^.+\|`(.+)\$.*$','$1')