Extract File Extension from file names in Excel – [Quick Tip]

What if your manager gave you a file with thousands of file names and you have to extract file extensions? If you are following our Excel tutorials on regular basis then it won’t be a problem as we have covered few of the ways that can be used to carry out this task.

Get Excel tutorials in your inbox
If you still haven’t subscribed to our Excel newsletter then don’t forget to signup and get all the Excel tutorials right in your inbox when they are published. Click here to signup

General knowledge is an asset – Approach to solution

In windows environment we have a relaxation that file extension are preceded by a period (full stop or dot) and even more than this dot cannot be used when naming the file. Therefore, dot has a special meaning as it separates the file name from file extension. And this is exactly what we are going to use to extract the file extensions.

Using Text to Column feature

Step 1:  Select the data from which you want to separate file extensions using mouse or keyboard

Step 2: In the ribbon above click Data tab > go to Data tools > and click Text to columns button. A dialogue box will appear

Step 3: Make sure Delimited option is selected and click Next button.

Step 4: Check Other option and in the input box insert dot by hitting period button on the keyboard. Uncheck any other option if it is checked. Click Next button and then Finish button.

File extensions are now separated from file names into another column quicker than you double click an icon.

However, it does have some limitations which may push you to deny using this feature. Suppose you want extensions but don’t want to change the content of original cells? In that case this solution is not really a good idea. This brings us to couple of following solutions.

Using RIGHT function

Most of the extensions consist of three letters for example JPG, TXT, PNG, BMP, MP3. And as extensions are always mentioned at the end of file name therefore we can use RIGHT function to fetch us last three characters in a text string.

For example if the fill name is in cell A1 then in B1 I will put this formula and hit Enter key:

=RIGHT(A1,3)

Now you can drag the fill handle to copy the same formula down to the range you want or simply double click to paste the same function down to complete range. And this formula will fetch you the file extension in every file name without any problem.

But what if file extension consists of more than 3 characters? Like XLXS, JPEG, TAX2011 etc. If this is the case then above formula is limited to get only last three characters in the text string and won’t work. Continue reading to sort this with ease.

Using RIGHT function with a twist

This solution is a bit mixture of general knowledge + common sense + excel function to get the job done.

Let me give you the formula we can use and then I will explain the reason behind it:

If file name is in cell A1 and if I want cell extension in cell B1 then B1 will have this formula:

=RIGHT(A1,LEN(A1)-FIND(".",A1))

We do know that file extension is preceded by a period. So if some how we make a formula that finds the dot in a text string and then from that point fetch the following characters then we can get the file extensions of variable length.

To find the dot in a text string we can use FIND function and it will give us the position of dot within text string. But problem with FIND function is that locates the intended text from left to right instead of right to left. Using FIND function we can easily get the file name as it is on the left of dot but not the file extension as it is lying on the right side of the dot.

For example if file name is: Project1.xlsx and you use FIND function to find “.” then the result will be 9 as dot is sitting at 9th position starting from left. Now this could have helped us immensely if FIND was able to start counting from right to left as we would have embedded FIND function straight in the RIGHT function to get the file extension.

Therefore we have to use a little trick here.

Here is the formula again:

=RIGHT(A1,LEN(A1)-FIND(".",A1))

And the name mentioned in cell A1 is suppose Project1.xlsx. Formula works as follows:

  1. LEN function is counting characters in cell A1 that are: 13
  2. FIND function is finding the position of dot in cell A1 which is: 9
  3. 13-9 = 4 will be the result after deducting the result of LEN and FIND.
  4. RIGHT function will now extract 4 characters out of text string starting from the right hand side.

Easy right? Yes it is!

So here you have three easy ways to extract file extensions out of file names. Now its up to you which one you like.

3 COMMENTS

  1. WRONG

    1st A period (dot) “.” can be within the file name, indeed, there can be many of them.
    2nd A filename can start with characters that confuse excel into considering the data to be a date, or a number.
    (OK specifying the cell to be “Text2 can deal with that.)
    3rd A filename can start with a character that makes excel consider the value to be a function.
    (OK specifying the cell to be “Text2 can deal with that.)
    4th A filename can start with a character that excel considers means the data is text, and is not (always) to be considered as part of the text.
    (consider the ‘ character! )
    5th File types using the shortname are limited to 3 characters with the first part of the name being limited to 8 characters
    (OK directories can be up to 11 characters, and do NOT have a type)
    6th A file does not have to have a ‘type’ following a period/dot
    7th A filename entry does not have to have a period/dot
    8th File types using the longname are limited to 256 characters.
    9th The filenames could be Extended form ( using the ? prefix up to 32000 characters long)
    10th The filenames may not actually be from a windows environment.
    11th Filenames can be just the period.
    12th Filenames may not be in the codepageset that is used within your version of Excel, Windows, or the facility used to generate the ‘text’ list.
    (The filenames may be converted by the OS or App into different characters than the OS managing the actual files uses.)

    Additional considerations
    Filenames can start with spaces non-space whitespace characters, special characters, or even emoticons
    they can include “” or ‘ or ” or even the MSWord/wordpad concept of ‘intellijunt” quotes
    From experience – filenames where there is no constraint specified on what will be processes are a fundamental pain

  2. The formula might work nicely for a single ‘.’ in the file name. However, it will not work as intended for other cases where the file name is a manifest with extension like smbdirect.sys.mui

    Formula in A1
    =RIGHT(A1,3)

    More examples: =IF(OR(RIGHT(B25971,3)=”dll”, RIGHT(B25971,3)=”sys”, TRUE,FALSE)

  3. Rahim Zulfiqar Ali

    One more formula to find / extract the files extensions from a cell,
    A1: First_name.xlsx
    A2: Second_name.zip
    A3: 3.docx

    Formula in A4
    A4: =MID(A1,FIND(“.”,A1)+1,99)

LEAVE A REPLY

Please enter your comment!
Please enter your name here