Adjust an incoming text box's values for different action tools

[felixlam]

Hi all.

Does anyone know of a way to alter an user's inputted text box values to match for different action tools? For example, I am having user's enter the date they want to analyze and pull data for entered in the format YYYYMMDD and want to be able to pull just the year values from that input for a different action tool. Also I am using the replace a specific string function (update value).

[apathetichell]

What happens when you use update value with formula and use a formula referencing [#1]?

[felixlam]

I considered that previously, but could not figure out a way to use the left or right functions with the user entered values. Maybe I was just not referencing it properly or writing the formula properly so if this is a solution you know of that would work perfectly. 

For reference, I have an incoming textbook which users enter their date value in YYYYMMDD format so I would be using a formula that looks something like LEFT([this users input].4) in order to pull the year. I guess then if you know what to accurately put for [this users input] that would work. Please let me know and thanks!

[apathetichell]

replace value with formula

padleft(tostring([#1]),4) 

my hunch is this is only part of your formula or whatever you are replacing - so this part works - but it's not combining in with the rest of your logic - ie share a screen shot of what you have there now - what you want. what's not working.

[felixlam]

This a screenshot of the file path I am working with. Essentially trying to pull the year, months and so forth to update into that file path. I also worked on a more manual updating method which I have also shared (it is still in progress but the general idea is there), but just trying to see if I can do it with the formula option.

[apathetichell · Accepted solution]

copy your entire excel location into the action tool (ie z://....)

turn it into two parts in quotes - one before/one after your year:

"z:///.../" +left([#1],4)+"{{part 2}}" - that entire expresion must be the formula you are using to replace your value.

note - this means that you have to have a file with that name/that sheet in that directory - your will get an error. I would not do that that - but that should accomplish what you wanted.