REPLACE([Destination], LEFT([Destination], FINDSTRING([Destination], "|")), LEFT([#1], FINDSTRING([#1], "|")))
Can anyone help me on how to read this formula in action tool? I am having some trouble in understanding it. Thank you so much!!
@jiawenmai The formula replaces the source file path with the destination file path and keeps the sheet name as it is. Basically it keeps the string '|||From Billings$' and replaces the rest of the string with the new one.
LEFT([Destination], FINDSTRING([Destination], "|")) - returns the string c:\.....\Book1.xlsx
LEFT([#1], FINDSTRING([#1], "|")) - does the same as above formula but this time the data is from the interface tool
finally the Replace function replaces all the path with the new one
That makes so much more sense now. Thank you very much for explaining it!