7 Convenient Ways to Extract Text Before Character in Excel

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.

excel extract text before character dataset

  • 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.

Putting Replace All

  • Now we press OK.

Pressing OK in excel extract text before character

  • Now we will find the extracted results.

Using the Find and Replace Tool

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)

Implementing LEFT and FIND Functions

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

Implementing LEFT and FIND Functions for excel extract text before character

🔨  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)

SUBSTITUTE Function to Extract n-th Occurrence

  • 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.

 Utilizing Text to Column Feature

  • 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.

Using Text to Column Feature step 2

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

Step 2

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

Final step

  • Here is our extracted data.

Utilizing Text to Column Feature for excel extract text before character

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.

Utilizing TEXTBEFORE Function

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.

Extracting Text Before Nth Occurrence of Character

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.

excel extract text before character to Return Text Before Last Occurrence of Delimiter

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.

excel extract text before character

🔨  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.

excel extract text before character

🔨  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.

(Visited 66 times, 1 visits today)
Tanzim

Tanzim

My name's Tanzim. My articles are meant to help you learn more about how Microsoft Excel works and how to use its different features. I enjoy gaming and hope that some of my knowledge will help me provide you with some interesting articles despite some tiresome technical terms.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo