community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE
SOLVED

Is it possible to make an excel spreadsheet TAB a FIELD option? PLEASE HELP

Highlighted
Meteor

I can't find anything on this... please help.

 

This is the full formula i'm trying to recreate, just having trouble finding CELL("filename")

 

=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")

When you input your data, choose the configuration option to Output File Name as Field and use the Full Path option... you can then parse out the tabname at the end of the FileName field after the ||| delimiter. For example, FileName would show up as C:\Users\NJohnso128\Desktop\Alteryx101SampleData.xlsx|||`Manager by Region$` and you could use Text to Columns to split out the 'Manager by Region$' portion, and some text formulas to get rid of the ' and $ punctuation.

 

Capture.JPG

 

NJ

Meteor

I'm a total beginner here at this, is there a way you can show me how to do this using Text to Columns and Text formulas?

Alteryx
Alteryx

The easy way with normal text formulas  to get the part after the Pipes would be 

 

ReplaceChar(Right([Filename],Findstring(ReverseString([Filename]),'|')),"'$","")

 

Split out, this is....

ReplaceChar(

Right([Filename],Findstring(

ReverseString([Filename]),'|')),

"'$","")

 

This will find the number of characters after the pipe (Line 3), take that many characters from the right hand side (Line2), and then replace the quotes and $ with nothing (Line1&4).

 

Alternatively, a T2C tool splitting to 5 columns and then take the fifth and replace characters.

Labels