Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

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

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

3 REPLIES 3
NicoleJohnson
ACE Emeritus
ACE Emeritus

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

Davidmcho
7 - 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?

KaneG
Alteryx Alumni (Retired)

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