13

I have a column containing file paths. I want to make another column which contain the extensions of the paths only. How can I do this in Microsoft Excel?

Improve this question
1
  • 1
    For anyone that's looking how to to this in Google Spreadsheet, you can use REGEXEXTRACT(A1, "\.([^.]*)$")
    – Joe
    Aug 9, 2015 at 9:21

8 Answers 8

Reset to default
22 
=REPLACE(RIGHT(A1,5),1,SEARCH(".",RIGHT(A1,5)),"")

This formula wil be useful if you have more than one dot in filename

Improve this answer
3
  • 2
    Note that this only works for file extensions that are four characters or shorter.
    – Tim Robinson
    Mar 21, 2013 at 16:26
  • 1
    You can just change the 5 to a 6 or whatever, right?
    – THE JOATMON
    Mar 11, 2015 at 14:45
  • Nope, that causes issues.
    – THE JOATMON
    Mar 11, 2015 at 15:11
7

Taken from another answer (changed to look for dots and not spaces):

=RIGHT(A1,LEN(A1)-FIND("|",SUBSTITUTE(A1,".","|",LEN(A1)-LEN(SUBSTITUTE(A1,".","")))))
Improve this answer
1
  • works a treat! Thanks for the quick copy/paste solution. Works for file extensions with 2 to 7 character file extensions with full paths and namespaces notation in filename.
    – Zephan Schroeder
    Jul 21, 2015 at 6:01
4

Cyril answer works only if the extension is 3 or 4 characters and user273281 answer did not work when there were . (periods) in file name.

Hence I figured out a new way of achieving this, 

=TRIM(RIGHT(SUBSTITUTE(TRIM(A1), ".", REPT(" ", LEN(TRIM(A1)))), LEN(TRIM(A1))))

Note : user273281's aswer did not work for the case where file name was 1. Macro based file.zip . It returns . Macro based file.zip

Improve this answer
1

I came across this, which is the simplest solution I've seen...

=SUBSTITUTE(RIGHT(SUBSTITUTE(A5,".",REPT(".",50)),50),".","")

It works because it separates any characters after the last period away from all other characters by 50 periods, or whatever number you choose. You can then select the rightmost 50 characters knowing that you are selecting nothing but your extension characters and a bunch of periods. Substitute out the periods and you're set to go.

Source

Alternative: XML Parsing

A neat trick I sometimes use for string parsing in general is to leverage the FilterXML() function (Excel 2013 and later). The basic strategy is to use Substitute() to format your string in a way that it is parsed across elements in an xml string, and then you can use xpath syntax to conveniently navigate your parsed elements. Using this strategy, getting an extension would look like this...

=FILTERXML("<A><p>" & SUBSTITUTE(A1,".","</p><p>.")&"</p></A>","//p[last()]")

If you're not familiar with xml, this can seem intimidating, but if you can grasp what's going on, I find it to be cleaner, more flexible, and easier to remember than the alternative approaches using len(), substitute(), etc. One reason why it's nicer is because there's only one cell reference.

Illegal Characters

There are two characters that are allowed in paths but not in xml: & and '

The equation above will work if these characters are not present, otherwise, they will need to be handled something like this...

=FILTERXML("<A><p>" & SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(J8,"'",""),"&",""),".","</p><p>.")&"</p></A>","//p[last()]")

Example

Suppose we have a nasty file path like this:

C:\Folder1\Folder2\(ugly characters !@#$%^()_+={};;,`)\two.dots.LongExt

1.) The Substitution() portion will convert it to an xml string like this...

<A>
    <p>
        C:\Folder1\Folder2\(ugly characters !@#$%^()_+={};;,`)\two
    </p>
    <p>
        .dots
    </p>
    <p>
        .txt
    </p>
</A>

2.) Once formatted like this, it's trivial to pick out the last p element using the xpath syntax //p[last()].

Improve this answer
0

Assuming you have nicely formatted extensions, you can do this quickly as =RIGHT(A1,3). A more robust solution would find the index of the rightmost . and then extract the remaining characters.

Improve this answer
3
  • Reading your question that is the answer I would have given.
    – VerGuy
    May 17, 2010 at 6:59
  • I think the real question here is: how to find the rightmost "." in Excel.
    – petersohn
    May 17, 2010 at 7:06
  • The extension is not alsway in 3 characters. @pertersohn: Yeah, you're right.
    – Nam G VU
    May 17, 2010 at 8:58
0

I changed the above formula to get the dot with file extension. This is the formula after I changed it:

=REPLACE(RIGHT(A1,4),5,SEARCH(".",RIGHT(A1,4)),"")
Improve this answer
0

If a file name doesn't have an extension, then Adarsh's answer returns the entire name. My version returns an empty string if there is no extension.

=IFERROR(IF(FIND(".",A1),TRIM(RIGHT(SUBSTITUTE(TRIM(A1), ".", REPT(" ", LEN(TRIM(A1)))), LEN(TRIM(A1)))),""),"")
Improve this answer
0

with the latest excel this should be possible using the following: | filepath | ext | |----------------------|-----| | file.txt | txt | | file.0.txt | txt | | /folder/file.txt | txt | | /folder.abc/file.txt | txt | | /folder.abc/file | | | /folder.abc/ | | | // | | | . | | | .. | | | ../.. | | | | | | . | | | a | | | .hidden | |

A1 = filepath

A2 = ext

B2:B100 = =IFERROR(TEXTAFTER(REPLACE(CHOOSECOLS(TEXTSPLIT(A2,"/"),-1),1,1," "),".",-1,,0,""),"") `

to support windows like paths the / in the A2 cell can be replaced with \

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .