Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • How to Create One and Two Variable Data Tables in Excel
  • Conditional Formatting Icon Sets Examples in Excel
  • Calculate Conditional Percentile ‘IF’ in table in Excel
  • Excel Frequency Function Example
  • How To Filter Data in Excel

References

  • Find Closest Match in Excel Using INDEX, MATCH, ABS and MIN functions
  • Perform case-sensitive Lookup in Excel
  • LOOKUP function: Description, Usage, Syntax, Examples and Explanation
  • How to use Excel VLOOKUP Function
  • How to get last row in mixed data with blanks in Excel

Data Validations

  • Excel Data validation don’t exceed total
  • Excel Data validation number multiple 100
  • Excel Data validation specific characters only
  • Excel Data validation date in next 30 days
  • Excel Data validation require unique number

Remove file extension from filename in Excel

by

To remove a file extension from a file name, you can use a formula based on the LEFT and FIND functions.

Formula

=LEFT(filename,FIND(".",filename)-1)

Note: because this formula finds the first occurrence of “.”, it will remove all file extensions when there are are more than one.

Worked Example:   Split text and numbers in Excel

Explanation

In the example shown, the formula in C5 is:

=LEFT(B5,FIND(".",B5)-1)

How this formula works

The core of this formula is the LEFT function which simply extracts text from the file name, starting at the left, and ending at the character before the first period (“.”).

=LEFT(filename,characters)

The FIND function is used to figure out how many characters to extract:

FIND(".",B5)-1

Find returns the position of the first match (6 in the first example) from which 1 is subtracted. The result, 5, goes into LEFT like this:

=LEFT(B5,5)

and the LEFT function returns the first five characters from the left: “Happy”.

Worked Example:   Split numbers from units of measure in Excel

 

Worked Example:   How to get last word in a cell in Excel

Post navigation

Previous Post:

Put names into proper case — Manipulating NAMES in Excel

Next Post:

Create One-dimensional and Two-dimensional Array

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • Extract multiple matches into separate rows in Excel
  • How to use Excel NOT Function
  • Return blank if in Excel
  • IF function: Description, Usage, Syntax, Examples and Explanation
  • IF with wildcards in Excel

Date Time

  • Convert Excel time to decimal seconds
  • Get last weekday in month in Excel
  • DATEDIF function: Description, Usage, Syntax, Examples and Explanation
  • Get week number from date in Excel
  • Convert date to Julian format in Excel

Grouping

  • Group arbitrary text values in Excel
  • How to randomly assign data to groups in Excel
  • Map text to numbers in Excel
  • Categorize text with keywords in Excel
  • Map inputs to arbitrary values in Excel

General

  • Delete Blank Rows at Once in Excel
  • Find, Select, Replace and Go To Special in Excel
  • AutoFit Column Width, AutoFit Row Height in Excel
  • How to calculate percentage discount in Excel
  • Freeze and Unfreeze Panes in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning