Extract text before/after space or comma in Excel - Easy Guide

Extracting text before or after a specific character (such as a space or comma) is a common task in Excel. Whether you're preparing a dataset for analysis or simply need to isolate certain elements of your text data, Excel offers several functions to accomplish this task. In this article, we will explore how to extract text before or after the first occurrence of a space, comma, or any other character using Excel formulas.

Extract text before or after first space /comma / other character

• By using formulas in all Excel version
• By using Kutools for Excel in all Excel version
• By using TEXTBEFORE / TEXTAFTER functions in Excel 365

Extract text before or after a specific character by using formulas

To extract the text before or after the first occurrence of a specific character, such as a space or comma, in Excel, you can use the following simple formulas.

Extract text before the first space or comma

1. Copy or enter the following formula into a blank cell where you want to locate the result, then, press "Enter" key to get the result. See screenshot:

   =LEFT(A2,(FIND(",",A2,1)-1))

   
   Tip: In this formula, you can replace the delimiter comma (,) with any other separator as needed.
2. Then, select the formula cell, and drag the fill handle down to fill the formula to other cells, all the text before the first comma has been extracted at once. See screenshot:
   

Extract text after the first space or comma

To extract text after the first space or comma, you can use a combination of the MID, FIND and LEN functions.

1. Copy or enter the following formula into a blank cell where you want to locate the result, then, press "Enter" key to get the result.

   =MID(A2, FIND(",", A2) + 1, LEN(A2))

   
   Tip: In this formula, you can replace the delimiter comma (,) with any other separator as needed.
2. Then, select the formula cell, and drag the fill handle down to fill the formula to other cells, all the text after the first comma is extracted at once. See screenshot:
   

Extract text before or after a specific character by using Kutools for Excel

Are you tired of complicated formulas and endless manual adjustments in Excel? Say goodbye to the hassle and hello to efficiency with Kutools for Excel! Our powerful add-in simplifies your workflow, allowing you to extract text before or after any specific character with just a few clicks.

After downloading and installing Kutools for Excel, please click "Kutools" > "Text" > "Extract Text" to open the "Extract Text" dialog box.

In the dialog box, under the "Extract by location" tab:

1. Select the data range that you want to extract text;
2. In the "Options" list box, check "Before the text" or "After the text" option, and type the delimiter you want to extract text based on;
3. Then, click "OK" Button.
   

Another prompt box will pop out to remind you select the cells to put the result, and then, click "OK", all the text before or after the first comma has been extracted, see screenshot:

Extract text before or after a specific character by using formulas in Excel 365

With the introduction of new functions in Excel 365, such as TEXTBEFORE and TEXTAFTER, this process has become more straightforward. This section will walk you through the steps to efficiently extract text before or after a specific character using these functions.

Extract text before the first space or comma with TEXTBEFORE function

The TEXTBEFORE function extracts the text that appears before a specified delimiter. The syntax of the TEXTBEFORE function is:

TEXTBEFORE(text, delimiter, [instance_num])

• text: The text string from which you want to extract characters (e.g., A2).
• delimiter: The character or string before which you want to extract the text (e.g., ",").
• instance_num: Optional. Specifies which instance of the delimiter to use if it appears multiple times. Default is 1.

1. Apply the TEXTBEFORE function to extract the text before the first comma:

   =TEXTBEFORE(A2, ",")

   
2. Then, select the formula cell, and drag the fill handle down to fill the formula to other cells.
   

Extract text after the first space or comma with TEXTAFTER function

The TEXTAFTER function extracts the text that appears after a specified delimiter. The syntax of the TEXTAFTER function is:

TEXTAFTER(text, delimiter, [instance_num])

• text: The text string from which you want to extract characters (e.g., A2).
• delimiter: The character or string after which you want to extract the text (e.g., ",").
• instance_num: Optional. Specifies which instance of the delimiter to use if it appears multiple times. Default is 1.

1. Apply the TEXTAFTER function to extract the text after the first comma:

   =TEXTAFTER(A2, ",")

   
2. Then, select the formula cell, and drag the fill handle down to fill the formula to other cells.
   

Summary

Extracting text before or after a specific character (such as a space or comma) is a common task in data processing in Excel. In this article, we explored several methods for extracting text, including using formulas in all versions of Excel, using the Kutools for Excel add-in, and using the TEXTBEFORE and TEXTAFTER functions in Excel 365.

● Extracting Text Using Formulas
In all versions of Excel, you can use functions like LEFT, FIND, MID, and LEN to extract text before or after a specific character. These formulas are effective but can sometimes be complex, requiring multiple steps and precise syntax.

● Extracting Text Using Kutools for Excel
Kutools for Excel is a powerful add-in that provides over 300 advanced features, simplifying the handling of complex tasks. With the text extraction feature in Kutools, you can easily extract text before or after a specific character with just a few clicks, significantly boosting work efficiency.

● Extracting Text Using TEXTBEFORE and TEXTAFTER Functions
In Excel 365, the new TEXTBEFORE and TEXTAFTER functions make this process more straightforward. These functions are specifically designed to extract text before or after a specific character, with simple and understandable syntax, greatly reducing the need for complex formulas.

No matter which method you use, you can choose the most suitable tool for extracting text based on your specific needs. If you're interested in exploring more Excel tips and tricks, our website offers thousands of tutorials. Please click here to access them. Thank you for reading, and we look forward to providing you with more helpful information in the future!