Extract file extension from a path
IntermediateOperations teams often export file lists from a shared drive before sorting documents by type, checking naming standards, or routing files to the right workflow. A key cleanup step is separating the file extension so you can quickly identify spreadsheets, images, presentations, and text-based files.
In this exercise, column A contains shared-drive paths with extensions of different lengths. Some are 2 characters, some are 3, and some are 4, so a fixed RIGHT formula will not work reliably across the whole list.
Each path is set up so there is only one dot in the file name portion. Your goal is to find that dot, determine how many characters come after it, and then use that result to return the extension.
You will need to combine RIGHT, LEN, and FIND. Since the dot appears once in each file name, you can use FIND to locate it and LEN to count how many characters come after it.
Example:
=RIGHT(A1, 3)
That works only when the extension is always 3 characters long, which is not true in this sheet.
Your task
Use the audit table in A2:C14 to work through the file list.
Fill the helper column B3:B14 with the number of characters that appear after the dot in each path.
Then fill C3:C14 with the matching extension from each path in column A.
Keep both formulas dynamic so they work across the full file list.
Need some help?
Hint 1
The helper column should return the number of characters after the dot, not the total file name length. That helper result can then drive RIGHT in the extension column.
Hint 2
Use FIND to get the position of the dot, then compare that position to the total length of the text with LEN.
Hint 3
Once the helper column returns the character count after the dot, use RIGHT to return exactly that many characters from the end of the path.
Extract file extension from a path
IntermediateOperations teams often export file lists from a shared drive before sorting documents by type, checking naming standards, or routing files to the right workflow. A key cleanup step is separating the file extension so you can quickly identify spreadsheets, images, presentations, and text-based files.
In this exercise, column A contains shared-drive paths with extensions of different lengths. Some are 2 characters, some are 3, and some are 4, so a fixed RIGHT formula will not work reliably across the whole list.
Each path is set up so there is only one dot in the file name portion. Your goal is to find that dot, determine how many characters come after it, and then use that result to return the extension.
You will need to combine RIGHT, LEN, and FIND. Since the dot appears once in each file name, you can use FIND to locate it and LEN to count how many characters come after it.
Example:
=RIGHT(A1, 3)
That works only when the extension is always 3 characters long, which is not true in this sheet.
Your task
Use the audit table in A2:C14 to work through the file list.
Fill the helper column B3:B14 with the number of characters that appear after the dot in each path.
Then fill C3:C14 with the matching extension from each path in column A.
Keep both formulas dynamic so they work across the full file list.
Need some help?
Hint 1
The helper column should return the number of characters after the dot, not the total file name length. That helper result can then drive RIGHT in the extension column.
Hint 2
Use FIND to get the position of the dot, then compare that position to the total length of the text with LEN.
Hint 3
Once the helper column returns the character count after the dot, use RIGHT to return exactly that many characters from the end of the path.