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.
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words...
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum...
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns...
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Create Mailing List and Send Emails by Cell's Value...
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
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.
|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
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.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.
To post as a guest, your comment is unpublished.· 2 years agoHi,
I want to know the formula which can give me the text after few characters. how to retrive that?
can someone help with this
To post as a guest, your comment is unpublished.· 2 years agoDear 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.· 2 years agoHello,
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,
To post as a guest, your comment is unpublished.· 3 years agoSNJAI21470,,499,PO15135,8907617138985,Manuf
How to extract third comma seprate value in other cell
To post as a guest, your comment is unpublished.· 3 years agoThank you so much!! so helpful!
To post as a guest, your comment is unpublished.· 3 years agoI'm wanting to create a unique order number/side mark using text from different cells:
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!
To post as a guest, your comment is unpublished.· 3 years agoI 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.
To post as a guest, your comment is unpublished.· 3 years agoThis was very helpful...!!!!
To post as a guest, your comment is unpublished.· 4 years agoHi,
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.· 4 years agowhat 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.· 4 years agoIn 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?
To post as a guest, your comment is unpublished.· 4 years agoHello
How can I extract text after first space to next space ?
To post as a guest, your comment is unpublished.· 4 years agoTry Trim(A1) or Clean(A1) where A1 is the cell you want to clean
To post as a guest, your comment is unpublished.· 4 years agotry Trim(A1) or clean(A1)
where A1 is the cell you want to clean spaces from
To post as a guest, your comment is unpublished.· 4 years agoHi Sir
I want to know informatio about to remove space text after cell.
kindly suggest me good formula about it.
i have urgent need of this.