Skip to main content

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

Author: Sun Last Modified: 2024-07-02

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 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 is extracted at once. See screenshot:
Explanation of the formula:
  • FIND(",", A2, 1): This part is used to find the position of the first comma in cell A2. The FIND function returns the position of the comma where it first appears in the text.
  • FIND(",", A2, 1) - 1: Since we only want to extract the characters before the comma, we subtract 1 from the position of the comma.
  • LEFT(A2, FIND(",", A2, 1) - 1): Finally, the LEFT function extracts characters from the left side of cell A2 based on the number of characters obtained from the previous step.
 

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. See screenshot:
    =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:
Explanation of the formula:
  • FIND(",", A2): This part is used to find the position of the first comma in cell A2. The FIND function returns the position of the comma where it first appears in the text.
  • FIND(",", A2) + 1: Since we want to extract the characters after the comma, we add 1 to the position of the comma.
  • LEN(A2): This part is used to get the total length of the text in cell A2.
  • MID(A2, FIND(",", A2) + 1, LEN(A2)): Finally, the MID function extracts all characters from cell A2 starting from the position obtained above to the total length of the text.

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.

Kutools for Excel offers over 300 advanced features to streamline complex tasks, boosting creativity and efficiency. Enhanced with AI capabilities, Kutools automates tasks with precision, making data management effortless. Read More...         Free trial...

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:

Tip: The Extract Text feature not only enables the extraction of text before or after a specific delimiter but also facilitates the retrieval of the first or last 'n' characters and the extraction of only numbers from a text string, allowing you to select the best option for your needs.

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!


Extract each piece of text separated by space / comma or other delimiters

Unlock the power of your data with Kutools for Excel! Whether you're dealing with spaces, commas, or other delimiters, our Split Cells feature allows you to effortlessly extract and organize individual pieces of text. With just a few clicks, streamline your workflow and enhance your data analysis.
Read More...         Free trial...

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

Description


Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Comments (32)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
thanks a lot
This comment was minimized by the moderator on the site
hello olivia john
hello olivia john david
hello olivia john robert more

Excel contains the data we need, but I'd like a value of end to appear before words like as.

john
david
more
Please provide any formula's clarification.
This comment was minimized by the moderator on the site
Hi, Nandakumar, I do not understand you problem. Do you want to extract the end word from each string or like to add the end value of the front cell before words in next cell?
This comment was minimized by the moderator on the site
hello olivia john
hello olivia john david
hello olivia john robert more

Excel contains the data we need, but I'd like a value of end to appear before words like as.

hello olivia john john
hello olivia john david david
hello olivia john robert more more

Please provide any formula's clarification.
This comment was minimized by the moderator on the site
Tried your example, I needed to read from cell D2 and read something before a space

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

and got: #VALUE!
This comment was minimized by the moderator on the site
Try with ";" instead of ","
This comment was minimized by the moderator on the site
What is the first "data" location for the sentence in row 6? ..Please answer me 🙏
This comment was minimized by the moderator on the site
Thank you!
This comment was minimized by the moderator on the site
Muchas gracias
This comment was minimized by the moderator on the site
Hello, thanks for this article, but is there a way for a mid function to work on the second space?
This comment was minimized by the moderator on the site
Hi Aland,
For example there is a sentence in the cell B3, you can extract the text after the second space from this cell with formula =MID(B3,FIND(" ",B3,FIND(" ",B3)+1),256)
This comment was minimized by the moderator on the site
Mehar Kusum Manav Maa Singh Bisht

what is formula for separate above words...
This comment was minimized by the moderator on the site
Hi Mehar singh bisht,
Do you mean split “Mehar Kusum Manav Maa Singh Bisht”, and place every word in a separate cell? You can select the cell containing the content, and click Data > Split to Column (set the delimiter as space) to split it.
There are no comments posted here yet
Load More
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations