There may be times when you need to extract text before the character easily in Excel. For example, you may need to use an Excel formula or VBA for this purpose. Today, we’ll show you how to use Excel to combine multiple rows in one cell with examples.

## 📁 Download Excel File

To prepare this article, we used an Excel workbook, which is available for download. Additionally, you can edit and customize the data you enter while viewing the results.

## Learn to Extract Text Before Character in Excel with These 7 Easy Ways

Excel has a lot of functions that make it easy to get the text before a character. We’ll learn how to use them in this article.

**Method 1**### 1. Using Find and Replace Tool

There are a lot of cool tools or features that come with Microsoft Excel that make it very useful. One of them is **Find **and **Replace**. We are going to pull out the texts that come before the character “**#**” from the dataset below.

**⬇️⬇️ STEPS ⬇️⬇️**

- First, we select the range
**B6:B9**.

- Then we copy it to the next column by pressing
**Ctrl**and**C**together and then paste it to cell**C6**.

- Now we select the new column’s data. Then we go to the
**Home tab**and click on**Find…**from the**Find & Select**.

- In the new box, we give
**#***. The asterisk here will work as a wildcard character.

- Later in the
**Replace**option, we put**Replace all**.

- Now we press
**OK**.

- Now we will find the extracted results.

**Method 2**### 2. Implementing LEFT and FIND Functions

The **LEFT function** is a type of **FIND function** that can find the leftmost text in a string in a given dataset. Here, we will use both the **LEFT **function and the **FIND **function together. Here we have a list of customer IDs and the items brought numbers are attached with “**_**”. We’ll take out the text before that character.

**⬇️⬇️ STEPS ⬇️⬇️**

- Let’s select cell
**C6**. - Then we put the following formula:

**=LEFT(B6,FIND(“_”,B6)-1)**

- Then we press
**Enter**and copy down the formula to find the result.

`🔨`

Formula Breakdown

`👉`

The **FIND **function gives you a number that tells you where in the whole text string the character “**_**” is.

`👉`

** **The **LEFT **function takes out the text from that number.

**Method 3**### 3. SUBSTITUTE Function to Extract n-th Occurrence

We can use the **SUBSTITUTE function** to find the nth place where a certain character is and pull out the text before that point. The **LEFT function** is a type of **FIND function** that can find the leftmost text in a string in a given dataset. Here, we will use both the **LEFT **function and the **FIND **function together. A lot of people use this function. Say we have a set of data. We’re going to pull out the parts of the string before the second space.

**⬇️⬇️ STEPS ⬇️⬇️**

- Let’s select cell
**C6**. - Then we put the following formula:

**=LEFT(B6,FIND(“^”,SUBSTITUTE(B6,” “,”^”,2))-1)**

- Then we press
**Enter**and copy down the formula to find the result.

`🔨`

Formula Breakdown

👉 Here, the **SUBSTITUTE **function switches out the second space for the “**^**” character.

`👉`

The **FIND **function gives you a number that tells you where in the whole text string the character “**_**” is.

`👉`

** **The **LEFT **function takes out the text from that number.

**Method 4**### 4. Utilizing Text to Columns Feature

Excel **Text to Columns** makes the dataset dynamic. Here we want to extract texts before the asterisk (*) from a dataset.

**⬇️⬇️ STEPS ⬇️⬇️**

- First, we select cell range
**B6:B9**.

- Then we copy it to the next column by pressing
**Ctrl**and**C**together and then paste it to cell**C6**.

- Now we select the new column’s data. Then we go to the
**Data**tab and click on**Text to Columns**from the**Data and Tools**.

- Later in the
**Wizard Step 1**, we select**Delimited**and press**Next**.

- In the next window, we put
**(*)**in the**Other**box and press**Next**.

- Lastly, we select General, then select where we want to see our extracted data in the
**Destination**Then we press**Finish**.

- Here is our extracted data.

**Method 5**### 5. Utilizing TEXTBEFORE Function

In this lesson, we are going to learn about the Excel **TEXTBEFORE function** and see how it can assist you in swiftly extracting text before any character. **TEXTBEFORE **function returns a text that occurs before any specific text.

**TEXTBEFORE****Function**#### 5.1. Extracting Text Before Delimiter

We are going to use the space character as the delimiter to get the text before a space in a string (” “).

**⬇️⬇️ STEPS ⬇️⬇️**

- Let’s select cell
**C6**. - Then we put the following formula:

**=TEXTBEFORE(B6, ” “)**

- Then we press
**Enter**and copy down the formula to find the result.

**TEXTBEFORE****Function**#### 5.2. Extracting Text Before Nth Occurrence of Character

We need to give the number for the instance num parameter to get the text that comes before the nth time the delimiter appears.

**⬇️⬇️ STEPS ⬇️⬇️**

- Let’s select cell
**C6**. - Then we put the following formula:

**=TEXTBEFORE(B6, “,”, 2)**

- Then we press
**Enter**and copy down the formula to find the result.

**TEXTBEFORE****Function**#### 5.3. Return Text Before Last Occurrence of Delimiter

We need to give the negative number for the instance num parameter to get the text that comes last before the delimiter appears.

**⬇️⬇️ STEPS ⬇️⬇️**

- Let’s select cell
**C6**. - Then we put the following formula:

**=TEXTBEFORE(B6, “,”, -1)**

- Then we press
**Enter**and copy down the formula to find the result.

**Method 6**### 6. Pull Out Characters from Right

Here we are going to collaborate **RIGHT**, **LEN, **and **FIND** functions. **RIGHT **gives you the last character or characters in a string of text, depending on how many characters you tell it to return. **LEN** tells you how many characters are in a string of text. The **FIND **function looks for one text string inside another text string and returns the number of the first character of the first text string.

**⬇️⬇️ STEPS ⬇️⬇️**

- Let’s select cell
**C6**. - Then we put the following formula:

**=RIGHT(B6,LEN(B6)-FIND(“,”,B6)-1)**

- Then we press
**Enter**and copy down the formula to find the result.

`🔨`

Formula Breakdown

`👉`

The **FIND **function gives you a number that tells you where in the whole text string the character “**_**” is.

`👉`

Here, the **LEN **function then tells you how many characters are in a string of text.

`👉`

** **Finally**, **the **RIGHT **function takes out the text from that number.

**Method 7**### 7. Utilizing MID Function to Extract Characters

In this method, we have used together **MID** and **FIND** functions. **MID** returns a certain number of characters from a text string, starting from the position you tell it to. The number of characters returned depends on how many characters you tell it to return. First, we find out what the text is, and then we take it out of the cell.

**⬇️⬇️ STEPS ⬇️⬇️**

- Let’s select cell
**C6**. - Then we put the following formula:

**=MID(B6,FIND(“-“,B6)+1,LEN(B6))**

- Then we press
**Enter**and copy down the formula to find the result.

`🔨`

Formula Breakdown

`👉`

The **FIND **function gives you a number that tells you where in the whole text string the character “_” is.

`👉`

Here, the **LEN **function then tells you how many characters are in a string of text.

`👉`

** **Finally**, **the **MID **function takes out the text from that number.

`📄`

Important Notes

`🖊️`

In Excel, the ribbon is used to access various commands. You can change a lot of things in the options dialogue window, like the ribbon, formulas, proofing, saving, and so on. So the appearance of worksheets on your device could be different from ours.

`🖊️`

A practice workbook is given so that you can practice yourself.

`🖊️`

All these exercises and tutorials are done on Microsoft Office 365. Some functions may be unavailable in previous versions.

`🖊️`

At the side of each Excel worksheet file, there is space where they can practice.

`📝`

Takeaways from This Article

`📌`

Reader will be able to extract text before characters in Excel with various methods.

`📌`

Readers can apply to use the **Find and Replace tool **in Excel to extract a text before any character.

`📌`

Readers can insert the **LEFT **and **FIND **functions to extract text before a character.

`📌`

They can also put the Excel **SUBSTITUTE **function before the nth time a character appears.

`📌`

Finally, viewers can use the “**Text to Columns**” feature to get the text before a certain character.

## Conclusion

That concludes today’s session. These are the methods for using Excel to extract text before a character. We are hopeful that this article will be extremely beneficial to you. Don’t forget to leave your thoughts and questions in the comments section and explore our website, **ExcelDen**, the best Excel solutions provider.