ExcelInsider Team
Extracting specific portions of text is one of the most common tasks when working with Excel. Whether you’re cleaning imported data, parsing IDs, or separating names, Excel offers multiple functions both simple and advanced that make text extraction effortless. These methods are invaluable for organizing large datasets, ensuring accuracy in reporting, or preparing values for further analysis.
In this tutorial, we’ll explore effective Excel functions such as LEFT, RIGHT, MID, SEARCH, FIND, SUBSTITUTE, TEXTBEFORE, and TEXTAFTER to extract exact pieces of text from any cell. Let’s get started
Steps to extract certain text from a cell in Excel:
➤ Place your text values in A2:A11.
➤ In B2, type this formula: =LEFT(A2,4)
➤ Press Enter and drag down.
Extract the First Four Characters with LEFT Function
The LEFT function extracts a specified number of characters from the beginning of a string. This is perfect when working with codes or IDs where the first few characters identify a product or category. We’ll use the following dataset:
Steps:
➤ Place your text values in A2:A11.
➤ In B2, type this formula:
=LEFT(A2,4)
➤ Press Enter and drag down.
The first four characters from each cell will be displayed.
Separate the Last Five Characters with RIGHT Function
In many Excel tasks such as parsing product IDs, location codes, or customer reference numbers, you often need to pull only the ending characters of a text string. The RIGHT function is the simplest and most efficient tool for this job. It retrieves characters directly from the end of any text value, ensuring you don’t have to manually count or split your data.
Steps:
➤ In B2, type:
=RIGHT(A2,5)
➤ Press Enter and copy down.
The final five characters from each string will appear.
Fetch Middle Segments Using MID Function
When working with structured data like product codes, location tags, or employee identifiers, you might need to grab characters that aren’t at the beginning or end of the text but somewhere in the middle. The MID function is perfect for this scenario, letting you specify the exact starting point and number of characters to extract.
Steps:
➤ In D2, enter:
=MID(A2,6,5)
➤ Press Enter and fill down.
This extracts five characters starting at position six.
Replace Text Extraction with TEXTBEFORE or TEXTAFTER Function
For users of Excel 365 or 2021, TEXTBEFORE and TEXTAFTER offer a modern, formula-light approach to extract text without complex nesting. These functions let you pull content that appears before or after a specified delimiter, such as commas, dashes, or spaces, making it ideal for parsing structured data like CSV imports, product lists, or concatenated codes.
Steps:
➤ In B2, enter:
=TEXTBEFORE(A2,"-",2)
➤ Press Enter and fill down.
This extracts text before the second comma.
➤ Alternatively, use this formula to extract text from later part In C2, enter:
=TEXTAFTER(A2,"-",2)
➤ Press Enter and fill down.
This extracts text after the second comma.
Retrieve Leading Segments with LEFT and SEARCH Functions
In many datasets, values are separated by delimiters such as dashes, underscores, or commas, for example, product codes or location identifiers. If you only need the first segment before the dash, combining LEFT with SEARCH makes this easy. The SEARCH function pinpoints the dash’s position, while LEFT extracts every character before it.
Steps:
➤ In B2, enter:
=LEFT(A2, SEARCH("-",A2)-1)
➤ Press Enter and copy down.
This retrieves all text before the first dash.
Standardize Text by Replacing Characters with SUBSTITUTE Function
When working with imported data, inconsistent delimiters or unwanted characters can make analysis difficult. The SUBSTITUTE function allows you to replace specific characters or text within a string, such as converting semicolons to commas, hyphens to spaces, or correcting typos. This method is ideal for cleaning datasets, preparing values for formulas, or ensuring consistency across large spreadsheets.
Steps:
➤ In B2, enter:
=SUBSTITUTE(A2,"-"," ")
➤ Press Enter and copy down.
All dashes are replaced with spaces in the extracted text.
Identify Exact Positions of Substrings with FIND Function
When analyzing long text strings, product descriptions, or concatenated codes, you may need to know where a specific keyword or substring begins. The FIND function in Excel pinpoints the starting position of a character sequence within a cell. This is especially helpful for parsing imported data, validating formats, or troubleshooting formula logic such as identifying where a location name, department code, or keyword like “Camp” occurs. Unlike SEARCH, which is case-insensitive, FIND is case-sensitive, making it perfect when letter case matters in your analysis or reporting.
Steps:
➤ In B2, type:
=IFERROR(FIND("Camp",A2), "")
➤ Press Enter.
The formula returns the starting position of “Camp” within the text and leaves the cell blank if no match is found.
Pinpoint Text Between Specific Delimiters with FIND and SUBSTITUTE Function
When dealing with highly structured data such as multi-level product codes, routing identifiers, or tracking strings containing several dashes, you might need to extract text that lies between a specific pair of delimiters, not just the first or second occurrence. By nesting SUBSTITUTE inside FIND function, you can temporarily replace specific dash occurrences with a unique character, then use MID to extract only the desired section.
Steps:
➤ In B2, enter:
=MID(A2,FIND("-",A2)+1,FIND("-",A2,FIND("-",A2)+1)-FIND("-",A2)-1)
➤ Press Enter and drag down.
This retrieves text between the first and second dash.
Isolate Text Between Two Dashes Using MID and SEARCH Functions
In datasets like order numbers, product tags, or employee codes, the information you need often sits neatly between two dashes. Rather than manually splitting or using multiple helper columns, you can pair MID and SEARCH to zero in on that exact segment. SEARCH identifies the positions of the first and second dashes, while MID extracts the characters between them.
Steps:
➤ In B2, type:
=MID(A2, SEARCH("-",A2)+1, SEARCH("-",A2,SEARCH("-",A2)+1)-SEARCH("-",A2)-1)
➤ Press Enter and fill down.
You’ll get the text located between the first and second dash.
Capture Trailing Segments After a Dash with RIGHT and LEN and SEARCH Functions
When working with structured data such as product codes, shipment IDs, or location tags, you may often need to extract everything that follows a delimiter like a dash. By combining RIGHT, LEN, and SEARCH, you can easily pull out the trailing portion of a string, no manual trimming or splitting required. SEARCH finds the dash’s position, LEN measures the string’s total length, and RIGHT extracts the remaining characters.
Steps:
➤ In B2, type:
=RIGHT(A2,LEN(A2)-SEARCH("-",A2))
➤ Press Enter and drag down.
The substring after the first dash will be extracted.
Frequently Asked Questions
Why do my extraction formulas return a #VALUE! error in Excel?
A #VALUE! error often occurs when the formula references a blank cell, a non-text value, or an unexpected character. Double-check your cell references, delimiters, and ensure text formatting matches your extraction method’s requirements.
Can I extract text dynamically without manually adjusting formulas for each row?
Yes. Excel functions like LEFT, MID, RIGHT, TEXTBEFORE, and TEXTAFTER automatically adjust for relative references. Simply drag the formula down a column. For advanced automation, use named ranges, structured references, or Power Query transformations.
What’s the difference between SEARCH and FIND for text extraction?
SEARCH is case-insensitive and supports wildcards, making it more flexible for general text parsing. FIND is case-sensitive and exact, useful when you need precise matches. Choose based on whether capitalization and character accuracy are important.
How do I handle extraction when my text contains inconsistent delimiters or formatting?
First, standardize your data using TRIM, SUBSTITUTE, or CLEAN to remove unwanted spaces or characters. If inconsistencies persist, consider combining functions (SEARCH, MID, VALUE) or use Power Query to transform and clean data before extracting.
Wrapping Up
In this tutorial, we explored multiple ways to extract specific text from a cell in Excel, from basic functions like LEFT and RIGHT to advanced options like TEXTBEFORE and TEXTAFTER. Whether you’re splitting product codes, isolating names, or cleaning imported data, these techniques provide flexibility and precision. Feel free to download the practice file and share your feedback.
