Extract text after two identical characters

[dininaus]

Hi All,

Probably simple I just cant figure it out how to do it, could someone please help me

I have column with filenames containing revision, status and format. I would like to extract all information except the file format. An example as below:

12345_ab.A.IFI.pdf
2432423.B.IFI.xlsm

I need to extract values as below

12345_ab.A.IFI
2432423.B.IFI

Thanks in advance.

 

[Tom.Jones]

One way:

=LEFT(A1,SEARCH("^^^",SUBSTITUTE(A1".";"^^^",3))-1)

 

[sandy666]

with PowerQuery (Get&Transform)

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ExtractBD = Table.TransformColumns(Source, {{"List", each Text.BeforeDelimiter(_, ".", {0, RelativePosition.FromEnd}), type text}})
in
    ExtractBD[/SIZE]

List		List
12345_ab.A.IFI.pdf		12345_ab.A.IFI
2432423.B.IFI.xlsm		2432423.B.IFI

 

[Aladin Akyurek]

Tom.Jones's suggestion

=LEFT(A2,SEARCH("^^^",SUBSTITUTE(A2,".","^^^",3))-1)

expects 3 dots in the input string.

Another option is control+shift+enter, not just enter, and copy down:

=LEFT(A2,MATCH(9.99999999999999E+307,MATCH(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),{"."},0))-1)

 

[dininaus]

Awesome ! Thank you so much guys for solving my problem!!!

 

[sandy666]

We are glad that we could help

Have a nice day