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.
|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
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 5 days agoHello, thanks for this article, but is there a way for a mid function to work on the second space?
- To post as a guest, your comment is unpublished.· 7 months agoMehar Kusum Manav Maa Singh Bisht
what is formula for separate above words...
- To post as a guest, your comment is unpublished.· 6 months agoHi Mehar singh bisht,
Do you mean split “Mehar Kusum Manav Maa Singh Bisht”, and place every word in a separate cell? You can select the cell containing the content, and click Data > Split to Column (set the delimiter as space) to split it.
- To post as a guest, your comment is unpublished.· 1 years agoIf i buy a licence then for how many days will it be valid for me?
- To post as a guest, your comment is unpublished.· 1 years agoHi Abbas Aziz,
For license issues, please contact firstname.lastname@example.org
- To post as a guest, your comment is unpublished.· 3 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.· 3 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.· 5 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.· 5 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.· 5 years agoHello
How can I extract text after first space to next space ?
- To post as a guest, your comment is unpublished.· 5 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.· 5 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.· 5 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.