Is there an Excel formula to extract text found between to characters at the end of a string?

Question

I would like to have the document number, version and file extension in their own columns.

I have a column (A) in excel that has data in the following format:

\\xxxxx\xxxxx\xxxxx\xxxxx\xxxxx\xxxxx\xxxxx\xxxxx\93363348_1.pdf

In the above,

Typically the above will be at the end of the string.

I used the below to extract the file extension.

=REPLACE(RIGHT(A1,5),1,SEARCH(".",RIGHT(A1,5)),"")

Any help would be appreciated.

Ok, now you just need another formula where you use `\ ` as delimiter and another one where you use `_` as delimiter. With this three formulas you can extract the desired parameters — , Dec 17 '21 at 17:13

score 2 · Answer 1 · answered Dec 17 '21 at 17:17

2

What you could try (using MS365) is:

Formula in B1:

=TRANSPOSE(FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"\","."),"_","."),".","</s><s>")&"</s></t>","//s[position()>last()-3]"))

It will spill your thee required values over into columns. For more information about FILTERXML(), I'd like to refer you to this older post.

answered Dec 17 '21 at 17:17

JvdV

1 Answers1