Extract File Name as Text from Stored Files in a Folder



📁 Topic: How to Extract File Name as Text from Stored Files in a Folder (Using Excel)

🎯 Objective:

Extract only the file names (without path) from a list of full file paths copied from a folder, using Excel’s Flash Fill.

🧭 Step-by-Step Process Flow

🔹 Step 1: Select All Files in a Folder

  1. Open the folder where your files are stored (e.g., PDFs, Word, Excel files).
  2. Press Ctrl + A to select all files.

🔹 Step 2: Copy File Paths

  1. While all files are selected, hold the Shift key, then:
  2. Right-click anywhere on the selection.
  3. Click on Copy as path
    🔗 This copies the full file path for each file, like:

arduino

CopyEdit

"C:\Users\YourName\Documents\Reports\Report1.pdf"

🔹 Step 3: Paste into Excel

  1. Open any Excel workbook.
  2. Click on Cell A1 (or wherever you want to paste).
  3. Press Ctrl + V to paste the list. You will see full file paths like:

arduino

CopyEdit

"C:\Users\YourName\Documents\Reports\Report1.pdf"

"C:\Users\YourName\Documents\Reports\Report2.pdf"

🔹 Step 4: Use Flash Fill to Extract File Names

  1. In the next column (e.g., B1), type the file name manually for the first row:

CopyEdit

Report1.pdf

  1. Go to B2, start typing the next filename or press Ctrl + E to activate Flash Fill.
    • Excel will auto-extract the file names from the full path in column A.
    • Your column B will now look like this:

python-repl

CopyEdit

Report1.pdf

Report2.pdf

Report3.pdf

...

✅ Done! You now have all file names extracted as plain text.

💡 What is Flash Fill?

Flash Fill is an Excel feature that automatically fills in values based on a pattern you start typing.

📍 How to Enable Flash Fill (if not working)

  • Go to File → Options → Advanced
  • Scroll to Editing Options
  • Check ✅ Automatically Flash Fill
  • Click OK

Or use the shortcut:
➡️ Ctrl + E (manually triggers Flash Fill)

🧾 Bonus Tip: Remove File Extensions (Optional)

If you want just the name without extension, like Report1 instead of Report1.pdf:

  1. In column C, type:

nginx

CopyEdit

Report1

  1. Use Flash Fill again (Ctrl + E).

Excel will auto-detect and remove the extensions from the filenames.

✅ Summary Workflow:

Step

Action

1

Select files in folder and Shift + Right-click → Copy as Path

2

Paste into Excel

3

Use Flash Fill in next column to extract file names

4

(Optional) Use another Flash Fill to remove file extensions