Download the Practice Workbook
5 Ways to Extract Text from a Cell in Excel
Method 1 – Using the LEFT Function to Extract Text from a Cell
The LEFT function extracts a particular number of characters from the left of a string.
Syntax of the LEFT Function:
=LEFT(text, [num_chars])We are going to extract the first 4 characters from the cells in column B.
Steps:
- Insert the following formula in Cell C5.
=LEFT(B5,4)
- Press Enter.
- Drag the Fill Handle over the range of cells C6:C9.
Read More: How to Extract Text after a Specific Text in Excel
Method 2 – Using the RIGHT Function to Extract Text
The RIGHT function extracts a specific number of characters from the end of a string.
Syntax of the RIGHT Function:
=RIGHT(text,[num_chars])We are going to extract the 4 characters from the right.
Steps:
- Use the following formula in Cell C5.
=RIGHT(B5,4)
- Press Enter.
- Drag the Fill Handle over the range of cells C6:C9.
Method 3 – Using the MID Function to Extract Text from a Cell in Excel
Syntax of the MID Function:
=MID(text, start_num, num_chars)
We have some codes divided into 3 parts. We are going to extract the middle 4 characters.
Steps:
- Insert this formula in Cell C5.
=MID(B5,6,4)
- Press Enter.
- Drag the Fill Handle over the range of cells C6:C9.
Read More: How to Extract Certain Text from a Cell in Excel VBA
Method 4 – Extract Text from a Cell Using Formulas
Example 4.1 – Extract Text Before a Particular Character
The Generic Formula:
=LEFT(text,SEARCH(char,cell)-1)We have a dataset consisting of some codes separated by a hyphen “-”. We’ll extract the text before the hyphen.
Steps:
- Use the following formula in cell C5.
=LEFT(B5,SEARCH("-",B5)-1)
- Press Enter.
- Drag the Fill Handle over the range of cells C6:C9
Read More: Extract Text Before Character in Excel
Example 4.2 – Extract Text after a Particular Character
The Generic Formula:
=RIGHT(text,LEN(text)-SEARCH("char",text))We want to pick the characters after the “-” character.
Steps:
- Use the following formula in Cell C5:
=RIGHT(B5,LEN(B5)-SEARCH("-",B5))
- Press Enter.
- Drag the Fill Handle over the range of cells C6:C9.
Read More: Extract Text After a Character in Excel
Example 4.3 – Extract Text Between Two Specific Characters from a Cell Using the MID and SEARCH Functions
We have a dataset of the Full names of some people. We are going to extract the middle name of each person.
Steps:
- Use this formula in Cell C5:
=MID(B5, SEARCH(" ",B5) + 1, SEARCH(" ",B5,SEARCH(" ",B5)+1) - SEARCH(" ",B5) - 1)
- Press Enter.
- Drag the Fill Handle over the range of Cells C6:C9.
Read More: How to Extract Text Between Two Commas in Excel
Method 5 – Using Find and Replace to Pick Text from a Cell
We have a list of emails and want to extract the username and domain name.
Case 5.1 – Extracting the Username from the Email
Steps:
- Copy the Text column values and paste them on the Extracted Text column.
- Select all those values.
- Press Ctrl + F. You will get the Find and Replace dialog box.
- In the Find What box type “@*”. It will select all the characters starting from the @.
- Keep the Replace With box blank.
- Click on Replace All.
- You’ll get a notification on how many replacements were made. Click OK.
- Close the Find and Replace box.
Case 5.2 – Extracting the Domain Name
Steps:
- Follow the previous case to make a copy of the mails and open the Find and Replace dialog box.
- In the Find What box, type “*@”. It will find all the characters from the beginning along with @.
- Keep the Replace With box blank.
- Click on Replace All.
- Press OK on the notification and close the dialog box.
Extract Text in Excel: Knowledge Hub
- How to Extract Text after Second Comma in Excel
- Extract Text After First Space in Excel
- Extract Text between Two Spaces in Excel
- How to Extract Text after Second Space in Excel
- Extract Text After Last Space in Excel
<< Go Back to String Manipulation | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
Hi, Shanto, I researched your site but can’t figure out how to fix my nagging problem for weeks now. What is a working formula that would convert MM/DD/YY date if it meets a condition. for example: in col. A, the date is 06/01/20; col. B is either condition 1, 2 or 3 Q: what is a formula to convert Col. A date to 06/GG/20 in column 3 if it meets the condition 2 in col. B? formula if it meets condition 3 and it change to 06/EE/20? and if 1, then back to 06/01/20. I would greatly appreciate any help on this? very tough! thanks in advance.
Hey Max, try the following VBA code
Sub Changing_Date_Format_Using_Condition()
Dim i As Integer
i = InputBox(“Enter a Number”)
If i = 2 Then
Range(“A1”).NumberFormat = “mm/dd/yyyy”
ElseIf i = 10 Then
Range(“A2”).NumberFormat = “dddd-dd-mmm-yy”
Else
Range(“A3”).NumberFormat = “mmm-yy”
End If
End Sub
Is this your required solution? If not then explain more about it. We will help you to get the result.
hi i want to delete only letter(a-z) in cell which is like below-
205apple=205
207dog=207
805cat=805
2007grape=2007
pls help me
Hi Rakesh! I hope this formula will do the task for you.
=TEXTJOIN("",TRUE,IFERROR((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*1),""))Here are more ways to extract just numbers from your data.
https://www.exceldemy.com/extract-only-numbers-from-excel-cell/
I know this is a few years old but I thought I’d give it a try:
With the First Date in Cell A1, Condition in Cell B1 and formula in Cell C1.
Formula in C1: =IFS(B1=1,TEXT(A1,”dd/mm/yy”),B1=2,TEXT(DAY(A1),”00″)&”/GG/”&TEXT(RIGHT(YEAR(A1),2),”00″),B1=3,TEXT(DAY(A1),”00″)&”/EE/”&TEXT(RIGHT(YEAR(A1),2),”00″))
Hello Suresh_Excelist,
Thank you for sharing this formula! It’s a clever use of the IFS function to handle multiple formatting conditions. The approach works well for customizing date formats based on specific criteria.
Just a quick note: if the IFS function isn’t available in an older version of Excel, the formula could be rewritten using nested IF statements for compatibility. Also, depending on the locale settings, the date format strings like “dd/mm/yy” might need adjustment to work as expected.
Feel free to share more such creative solutions!
Regards
ExcelDemy