## 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
**C****ell 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
**C****ell 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**

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/