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]

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]List[/td][td][/td][td=bgcolor:#70AD47]List[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]12345_ab.A.IFI.pdf[/td][td][/td][td=bgcolor:#E2EFDA]12345_ab.A.IFI[/td][/tr]

[tr=bgcolor:#FFFFFF][td]2432423.B.IFI.xlsm[/td][td][/td][td]2432423.B.IFI[/td][/tr]
[/table]

 

[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