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 200 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.
    Atul · 1 years ago
    Hi,

    I want to know the formula which can give me the text after few characters. how to retrive that?

    can someone help with this

    Regards/-
    atul
    • To post as a guest, your comment is unpublished.
      Mahwish · 11 months ago
      Dear Atul,

      If u need exactly after certain character, (say 20 words incl space). You can go for DATA tab, Then select the column you want changes... then go to TEXT TO COLUMNS ribbon. After that a dialogue box will open and u can see Fixed width, Select it. Click next, then u will see 10, 20, 30..., click on 20 (or how many words u need after), then next. FINISH.

      CAUTION: Insert some blank columns after the required column to avoid any data loss
  • To post as a guest, your comment is unpublished.
    Tim · 1 years ago
    Hello,
    Perhaps you can help me?
    I like your information above, but as usual I have a different split column issue.
    So... I'm looking for a way to split a cell of text into to. The split must not come more that 30 characters (Including spaces) from the left but must be at a space or the immediate previous space to the 30th character point.

    So if the cell contained: "EMERGENCY MU/INITIAL FILL TO CONDENSER CTRL VAL"
    The 30th Character is the "C" of Condenser so the cell will split between "TO" and "Condenser"
    How do I write this code please?
    Many thanks for your help,
    Tim
  • To post as a guest, your comment is unpublished.
    Pappu Singh · 1 years ago
    SNJAI21470,,499,PO15135,8907617138985,Manuf

    How to extract third comma seprate value in other cell
  • To post as a guest, your comment is unpublished.
    Chad · 1 years ago
    Thank you so much!! so helpful!
  • To post as a guest, your comment is unpublished.
    Linda · 1 years ago
    I'm wanting to create a unique order number/side mark using text from different cells:

    EXAMPLE:

    Account: AB
    Contractor: Buffalo Run
    Address: 11453 Main St.

    In this example lets say everything before the colons are the column name and to the right of the colon is the data within the column. The unique ID/sidemark I want to create is using these cells. so the result is: [b]ABBR11453[/b], the break down of this ID:

    [b]AB [/b]= extracted from Cell 1 (Accounts)
    [b]BR [/b]= extracted from Cell 2 (Contractor), 1st letter of the first 2 words in this cell i.e. B for Buffalo and R for Run
    [b]11453 [/b]= extracted from Cell 3, only the numbers in the address

    I've watched tutorials and using the CONCATENATE, LEFT, MID and other functions and I'm not quite getting it. Can you PLEASE HELP!