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
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.
(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.
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.
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:
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.
Best Office Productivity Tools
Supports Office/Excel 2007-2021 and 365 | Available in 44 Languages | Easy to Uninstall Completely
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need Is Just A Click Away...
Supercharge Your Excel Skills: Experience Efficiency Like Never Before with Kutools for Excel (Full-Featured 30-Day Free Trial)
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! (Full-Featured 30-Day Free Trial)