How to make a VBA code in Excel to copy and paste only certain values in a cell

Question

I have a project that deals with thousands of file names in Excel.

S:\Workgroups\APC Environmental Affairs\WATER\`New File Structure\PLANTS\HYDRO\HENRY\SPCC\correspondence\Henry SPCC NEW REVISION.msg

The complete file location is listed in the cells. I would need to copy over only the true file name "Henry SPCC NEW REVISION" into a new cell over

S:\Workgroups\APC Environmental Affairs\WATER\`New File Structure\PLANTS\HYDRO\HENRY\SPCC\correspondence\Henry SPCC Plans.msg

For this one I would copy over "Henry SPCC Plans". Basically I'm copying over the text between the last \ and the .file extension.

Is there a macro or special paste function I can use to help me with this?

score 0 · Answer 1 · edited May 23 '17 at 11:51

0

As part of tigeravatar's answer, you can use:

=MID(A1,FIND("@",SUBSTITUTE(A1,"\","@",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))+1,LEN(A1))

edited May 23 '17 at 11:51

Community

answered Jul 07 '15 at 15:04

Steven

1 Answers1