excel公式在文件路径文本字符串中找到零件号 [英] excel formula find part number in file path text string

问题描述

我提取了网络驱动器上的所有文件，并且在某些文件名中是部件号，部件号格式为0000-000000-00.现在，在此文件的600,000多个路径名中，我试图弄清楚如何从路径名中提取零件号.我认为mid公式可能会起作用，但是我对如何告诉它查找带有#格式0000-000000-00的部件并从路径中仅提取那14个字符的内容感到迷惑?

I have a extract of all the files on a network drive, and in the some file names is a part number, the part numbers format is 0000-000000-00. Now in the 600,000+ path names in this file I'm trying to figure out how to extract my part numbers out of the path names. I think a mid formula might work but I am at a loss on how to tell it to find anything with the part # format 0000-000000-00 and extract only those 14 characters from the path?

输入看起来像这样

c:\users\stuff\folder_name\1234-000001-01_ baskets_1.pdf
c:\users\stuff\folder_name\1234-000001-02_ baskets_2.pdf
c:\users\stuff\folder_name\1234-000001-03_ baskets_3.pdf
c:\users\stuff\folder_name\1234-000030-01_ tree_30.pdf
c:\users\stuff\folder_name\random text_1234-000030-02_ tree_30.pdf
c:\users\stuff\folder_name\more random stuff_1234-000030-02_ tree_30.pdf

我希望得到的输出

1234-000001-01
1234-000001-02
1234-000001-03
1234-000030-01

推荐答案

由于您拥有可以利用的模式，因此请使用:

Since you have a pattern we can exploit, use this:

=MID(A1,SEARCH("????-??????-??",A1),14)

查找模式的开头，然后返回14个字符.

Finds the start of the pattern and returns the 14 character after.

这篇关于excel公式在文件路径文本字符串中找到零件号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！