Use a formula tool:
1. REPLACECHAR(RIGHT([FILENAME],FINDSTRING(REVERSESTRING([FILENAME]),'|')),'`$','')
or
2. Substring([FileName],FindString([FileName],"`")+1,length([FileName])-FindString([FileName],"`")-3)
Both formulas achieve the same result. This only works if you have used fullpath as the filename
Regular expressions also work nicely:
@anayet1988 - Just throwing out an idea . . . might be too simple . . . have you tried the 'Import only the list of sheet names' option on the input. If you then also include the Full Path, you'll get both the Full Path and the Sheet Name already separate. If you then need to bring in data from all or specific sheets, you can use a filter to specify which sheets, and then a Dynamic Input Tool to bring in the data.
Thank you for everyone's responses