right trimming?

[kin1]

hello,i have a situation where i have a column of data that i want to split out, it's base on file locations and i only want the file name.example;c:\testfile\maincust\custa\file1.docc:\testfile\maincust\custb\important\important1.docc:\testfile\maincust\custb\important\responded\response1.docwith the example above, i only want to get the last portion to the right of the last "\". is there a way to do that? the result should befile1.docimportant1.docresponse1.docthanks!

 

[Rick Rothstein]

One of these formulas (which depending on if your filenames have leadigin or multiple embedded spaces in them) should do what you want....

No spaces
------------------------------------
=TRIM(RIGHT(SUBSTITUTE(A1,"\",REPT(" ",99)),99))

With spaces
-----------------------------------
=SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(A14," ",CHAR(1)),"\",REPT(" ",99)),99)),CHAR(1)," ")

 

[kin1]

that is really cool, thank you. i will have to experiment with the repeat function now.thanks!