Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or
0
0
0
s2smodern

How to extract text before/after space or comma only in Excel?

When you want to extract the text before or after the space from the list as shown as below, do you have a good way to get it done? Let me tell you some trick ways to extract text before or after space only in Excel.

Extract text before or after space or comma with formula

Extract text before or after space or comma with Kutools for Excel

Extract text before space or after space or any other separators

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. Click for 60-day free trial!
ad split cell separate text numbers


arrow blue right bubble Extract text before or after space with formula in Excel

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.

note ribbon 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

arrow blue right bubble Extract text before or after space with Kutools for Excel

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.

Kutools for Excel - Combines More Than 120 Advanced Functions and Tools for Microsoft Excel

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.

Free Trial Kutools for Excel Now


arrow blue right bubble Extract text before space and after space/comma separately at the same time

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:

Kutools for Excel - Combines More Than 120 Advanced Functions and Tools for Microsoft Excel

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.


arrow blue right bubbleDemo: Extract text before or after space,comma or other delimiters by Kutools for Excel

Tip: In this Video, Kutools tab and Enterprise tab are added by Kutools for Excel. If you need it, please click here to have a 60-day free trial without limitation!


arrow blue right bubbleRelative Articles:


Recommended Productivity Tools

Office Tab

gold star1 Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.

Kutools for Excel

gold star1 Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

gold star1 300 New Features for Excel, Make Excel Much Easy and Powerful:

  • Merge Cell/Rows/Columns without Losing Data.
  • Combine and Consolidate Multiple Sheets and Workbooks.
  • Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
  • Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
  • More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...

Screen shot of Kutools for Excel

btn read more      btn download     btn purchase

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Gus · 2 years ago
    I want to thank you, It took me over 3- 4 hours looking into different websites, help instructions and formula list, and finally you have the answer I needed. =LEFT(A1,(FIND(" ",A1,1)-1)) As quickbooks merged the part numbers with the descriptions in most of the inventory reports, I needed to build a report with vlookups and other formulas to get the inventory to show in days of stock but I was not able to extract the part number from the reports until I found your formula. Advertise it as a work around in Quickbooks reports and your website will be more popular.

    Cheers.
  • To post as a guest, your comment is unpublished.
    Trae · 2 years ago
    This was very helpful...!!!!
  • To post as a guest, your comment is unpublished.
    Starla Harrington · 3 years ago
    Hi,

    I am having trouble finding the formula for the following example I want to make a new column and have only the last name no first, middle initial, comma, or MD:

    John P. Smith, MD

    Thanks, Starla H.
  • To post as a guest, your comment is unpublished.
    Desiree · 3 years ago
    what is the purpose of the 256 at the end of the formula in the first example?
  • To post as a guest, your comment is unpublished.
    Desiree · 3 years ago
    In the first example about extracting the text after space only with the formula =MID(A1,FIND(" ",A1)+1,256). What is the purpose of the 256 in the formula?