Data minipulation, text after a symbol

[marckk]

Hi,
I need a formula to return all of the text after the first instance of a symbol. In cell A1 I have the following string:

"\\hou-fs-01\clients\ati\datajunc\data\Aetna\OE\PROD\secureftp.x12unz.ati.txt"

what I need to retun is everything after the last "\" so I need:

"secureftp.x12unz.ati.txt"

in the cell displaying the results from the formula. The column that contains the source data may have 3 "\" slash marks may have 10 so I don't have a set number. Any help or direction would be appreciated

 

[T. Valko]

Hi,
I need a formula to return all of the text after the first instance of a symbol. In cell A1 I have the following string:

"\\hou-fs-01\clients\ati\datajunc\data\Aetna\OE\PROD\secureftp.x12unz.ati.txt"

what I need to retun is everything after the last "\" so I need:

"secureftp.x12unz.ati.txt"

in the cell displaying the results from the formula. The column that contains the source data may have 3 "\" slash marks may have 10 so I don't have a set number. Any help or direction would be appreciated

Try this...

=SUBSTITUTE(RIGHT(SUBSTITUTE(A1,"\",REPT("\",100)),100),"\","")

 

[marckk]

Try this...

=SUBSTITUTE(RIGHT(SUBSTITUTE(A1,"\",REPT("\",100)),100),"\","")

That did the trick!!

Thank you so much! I've never used the "SUBSTITUTE" function, I never would have figured that out on my own!!!!!

 

[T. Valko]

That did the trick!!

Thank you so much! I've never used the "SUBSTITUTE" function, I never would have figured that out on my own!!!!!

You're welcome. Thanks for the feedback!