Parse out specific number in filepath

[brothaprice]

Hello:

I pasted several filepaths from a folder into one column in Excel. I want to parse out a varying eleven-digit number found in every filepath. What is the easiest way to do this?

Here are some sample paths:

P:\mppub\Street Name and House Numbering\ADDRESSING_PDF_ARCHIVE\150th_St_E_47083_3314010044_201209.pdf

P:\mppub\Street Name and House Numbering\ADDRESSING_PDF_ARCHIVE\E_Ave_R-4_38320_3362012054_201212.pdf

The eleven-digit number would be 3314010044 in the first sample and 3362012054 in the second.

 

[Rick Rothstein]

Is the number you want always followed by an underline character, 6 digits, a dot and a 3 character file extension?

Also, you say you want an eleven digit number, but your example is only 10 digits long. Do you really want a ten digit number or is your example flawed and you do, in fact, want an eleven digit number.

 

[sandy666]

with your examples

Name	TBD
150th_St_E_47083_3314010044_201209.pdf	3314010044
E_Ave_R-4_38320_3362012054_201212.pdf	3362012054

let
    Source = Folder.Files("D:\test\pathpdf"),
    TSC = Table.SelectColumns(Source,{"Name"}),
    TBD = Table.AddColumn(TSC, "TBD", each Text.BetweenDelimiters([Name], "_", "_", {1, RelativePosition.FromEnd}, 0), type text)
in
    TBD

change path to the folder suitably

 

[brothaprice]

Is the number you want always followed by an underline character, 6 digits, a dot and a 3 character file extension?

Also, you say you want an eleven digit number, but your example is only 10 digits long. Do you really want a ten digit number or is your example flawed and you do, in fact, want an eleven digit number.

Hello Rick. Thank you. You are right. It is 10 numbers. It is not always followed by 6 digits. Some files have 8 digits at the end instead of 6. YYYYMMDD instead of YYMMDD.

 

[brothaprice]

Hello Rick. Thank you. You are right. It is 10 numbers. It is not always followed by 6 digits. Some files have 8 digits at the end instead of 6. YYYYMMDD instead of YYMMDD.

The 10-digit number should always follow by the second to last underscore

 

[Rick Rothstein]

This formula will return the number (as text just in case there are ever leading zeros)...

=TRIM(LEFT(RIGHT(SUBSTITUTE(A1,"_",REPT(" ",400)),800),400))

 

[brothaprice]

This formula will return the number (as text just in case there are ever leading zeros)...

=TRIM(LEFT(RIGHT(SUBSTITUTE(A1,"_",REPT(" ",400)),800),400))

Thank you Rick. This worked well.

 

[brothaprice]

with your examples

Name	TBD
150th_St_E_47083_3314010044_201209.pdf	3314010044
E_Ave_R-4_38320_3362012054_201212.pdf	3362012054

let
    Source = Folder.Files("D:\test\pathpdf"),
    TSC = Table.SelectColumns(Source,{"Name"}),
    TBD = Table.AddColumn(TSC, "TBD", each Text.BetweenDelimiters([Name], "_", "_", {1, RelativePosition.FromEnd}, 0), type text)
in
    TBD

change path to the folder suitably

Thank you Sandy. I plan to try this in the future.

 

[sandy666]

You are welcome