Parsed Filename and Extension as Column

[SergioHB]

Hi all,

I have a .csv file that contains a path and filenames in it and I need to extract the file extensions in a different column. The thing here is that some of those paths contains data with multiple separators in it, then I can't rely on the Text.AfterDelimiter function as this is returning the part of the string after the first delimiter and also, cannot use the Text.End as there are extensions with two, three and four letters in it.

I guess by searching from the end of the path until it finds the first separator ('.') would be enough to return the correct value, but I can't make it.

Appreciate any help on this. Leave some examples of the info.

./TEST_PNG_File-0.png.Thumbnail.jpg
./TEST2_PNG_File-0.png.SmallImage.jpg
File.TEST ai..ai

Thanks

Sergio

[msauriol]

Another way I found is to duplicate the filename column into a new extension column, then use extract before (file) and after (ext) the delimiter, on the filename and extension columns. Specify the period as the delimiter in the extract screen, and under Advanced, select from the end of the input for both left and right filename parts.

[AilleryO · Accepted solution]

Hi,

If you use on Power Query the split Column button (tab Home),

you have an option to select to use the most left or most right delimiter.

If you use the most right, it should work.

Check that your column, is of Text type, to have all options available.

Let us know if it works,

[SergioHB]

Works really good @AilleryO .

Thanks for your help and quick reply.

Stay safe.

Sergio