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

Data Analysis

  • Excel Pie Chart
  • Conditional Formatting Icon Sets Examples in Excel
  • How to create Checklist in Excel
  • Conditional Formatting New Rule with Formulas in Excel
  • Use Data Form to input, edit and delete records in Excel

References

  • Get nth match with INDEX / MATCH in Excel
  • How to get first row number in range in Excel
  • How to use Excel MATCH Function
  • VLOOKUP function: Description, Usage, Syntax, Examples and Explanation
  • How to get relative column numbers in a range in Excel

Data Validations

  • Excel Data validation allow weekday only
  • Excel Data validation exists in list
  • Excel Data validation no punctuation
  • Excel Data validation must contain specific text
  • Excel Data validation number multiple 100

How to extract substring in Excel

by

To extract a substring with an Excel formula, you can use the MID function.

Note: In this example, we are calculating the end position in order to extract a substring with a literal start and end position. However, if you know the number of characters to extract, you can just plug in that number directly.

 Formula

=MID(A1,start,end-start+1)

Explanation

In the example shown, the formula in E5 is:

=MID(B5,C5,D5-C5+1)

which, on row 5, returns “Perfect”.

Worked Example:   Highlight cells that contain in Excel

How this formula works

In the example on this page, we are using the MID function to extract text based on a start and end position. The MID function accepts three arguments: a text string, a starting position, and the number of characters to extract. The text comes from column B, and the starting position comes from column C. The number of characters to extract is calculated by subtracting the start from end, and adding 1. In cell E6:

=MID(B5,C5,D5-C5+1)
=MID("Perfect is the enemy of good",1,7-1+1)
=MID(B5,1,7) // returns "Perfect"

Functions to extract substrings

Excel provides three primary functions for extracting substrings:

=MID(txt,start,chars) // extract from middle
=LEFT(txt,chars) // extract from left
=RIGHT(txt,chars) // extract from right

Finding start and end positions with a formula

In the example shown, start and end positions are based on hardcoded values. However, it is possible to calculate positions with the FIND function and SEARCH function.

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

  • IF, AND, OR and NOT Functions Examples in Excel
  • Nested IF function example in Excel
  • Check multiple cells are equal in Excel
  • How to use Excel AND Function
  • IF with boolean logic in Excel

Date Time

  • Display Days until expiration date in Excel
  • How to calculate quarter from date in Excel
  • List holidays between two dates in Excel
  • Calculate expiration date in Excel
  • Get date from day number in Excel

Grouping

  • How to randomly assign people to groups in Excel
  • Group times into 3 hour buckets in Excel
  • Group times into unequal buckets in Excel
  • Map text to numbers in Excel
  • How to randomly assign data to groups in Excel

General

  • How to set or clear a print area in Excel Worksheet
  • Zoom Worksheet in Excel
  • Mark Workbook as Final in Excel
  • How to Delete Cells, Row and Rows in Excel
  • Check if multiple cells have same value with case sensitive in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning