I currently am reading in files named #### $1000.xlsx or ### $1000.xlsx. I'm having the file name output as a field. Because it can be 3 or 4 digits I can just use the Left function. So I wanted to use the TrimRight function - TrimRight([FileName]," $1000") but for any file name that ends in 1, it is trimming that as well. So for 2 files named 1115 $1000 and 1921 $1000 I expect the result of the fomula ro return 1115 and 1921, but what I'm actually getting is 1115 and 192. I also tried with file 1151 $1000 and it returned the same issue with just 115 as the result. Am I missing something? Is the dollar sign throwing it off? Or can someone suggest a better way to do this? I supposed I could use a PadLeft and add a 0 to make the 3 digit number 4 and then use a Left to keep the first 4 characters but I'm just lost on why it's cutting that leading 1 when I put in quotes what I want trimmed.
Thanks!
