How To Extract Text Before/After Space Or Comma Only In Excel? (2024)

You can quickly extract the text before space from the list only by using formula.

Select a blank cell, and type this formula =LEFT(A1,(FIND(" ",A1,1)-1)) (A1 is the first cell of the list you want to extract text) , and press Enter button.

Tips:
(1) If you want to extract text before or after comma, you can change " " to ",".
(2) If you want to extract the text after space only, use this formula =MID(A1,FIND(" ",A1)+1,256).
(3) This method extracts text by the first space in specified cells. If more than one spaces exist in the cell, for example the "Katty J James", the formula =MID(A1,FIND(" ",A1)+1,256) will extract all characters after the first space.

Formula is too complicated to remember? Save the formula as an Auto Text entry for reusing with only one click in future! Read more… Free trial

Extract numbers (or text) only from one cell/column into different columns/rows

Kutools for Excel enhances its Split Cells utility and supports to batch extract all text characters or numbers from one cell/column into two columns/rows.

Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy a full-featured 30-day FREE trial with no credit card required! Get It Now

If you have installed Kutools for Excel, you can use its Extract Text utility to quickly extract text before or after space only from a list.

1. Select the list and click Kutools > Text > Extract Text. See screenshot:

2. In the pop-up dialog, type * and a space into the Text box, click Add button, only check this new added rule in the Extract list section, and click the Ok button.
Tip: If you want to extract text after space only, type a space and follow * into the Text box in this step.

3. In another popping dialog, please specify the first cell address of destination range you will output extracted data, and click OK button. Till now, you can see the texts before space have been extracted only.

Note: If you want to extract text before or after comma, you can type *, or ,* into the Text box.

Kutools for Excel supports another utility of Split Cell utility to help us extract both text before space/comma and text after space/comma, and output them into different cells. Please do as follows:

1. Select the list of text strings that you will extract from, and click the Kutools > Text > Split Cells.

2. In the opening Split Cells dialog box, specify the split type in the Type section, check one split separator in the Split by section, and click the Ok button. See screenshot above:
In our case, we check the Split to Columns option and Space option, and click the Ok button. And now the list of text string is split to column or rows based on space. See screenshot below:

Note: If you need to extract text before or after comma, please check the Other option in the first Split Cells dialog box, and type the comma "," into below box.

Split text by space/comma/delimiter in Excel

Extract first / last / nth word from text string in Excel

Extract text between commas/brackets/parentheses in Excel

Best Office Productivity Tools

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

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!