如何在Excel中创建VBA代码以仅复制和粘贴单元格中的某些值

时间:2022-07-25 22:18:13

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

我有一个项目,处理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

完整的文件位置列在单元格中。我需要将真正的文件名“Henry SPCC NEW REVISION”复制到一个新的单元格中

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.

对于这个,我会复制“亨利SPCC计划”。基本上我在最后的\和.file扩展名之间复制文本。

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

我可以使用宏或特殊粘贴功能来帮助我吗?

1 个解决方案

#1


0  

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

作为tigeravatar的答案的一部分,您可以使用:

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

#1


0  

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

作为tigeravatar的答案的一部分,您可以使用:

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