Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to remove all characters after the first/last space from cells in Excel?
In the article, we may go to remove or delete all characters after only the first and last space in any sentence which we have inserted in the cells in Microsoft Excel. The users must analyze the data to remove or delete the first or last characters from the left side in all cells either by the formulas or by using the Ku-tools tab also. The steps by step explanations are given in both examples to understand the concept clearly. Users must write the user defined formula correctly to obtain the accurate result.
Let’s explore the examples one by one −
Example 1: By Using the Formula
Step 1
Deliberate the Excel worksheet. Open the Microsoft Excel sheet and insert the data from the cells A1 to A7 as you need as shown below.
Step 2
In the Excel sheet, locate the pointer in cell B2 then enter the formula to remove the first character from the left side. So enter the formula that is =LEFT(A2,FIND("^^",SUBSTITUTE(A2," ","^^",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))-1) then press enter tab that will display the result. It will remove after the last space from the sentence as shown below.
Step 3
In the Excel sheet, locate the pointer in cell B2 and use the fill handle to drag the cell. Drag cell B2 to cell B7 then it will automatically display the result with the formula as shown below.
Step 4
In the sheet, place the pointer in cell C2 then enter the formula to remove the after the first space from the left side. So enter the formula that is =LEFT(A2,FIND(“ ”,A2)-1) then press enter tab that will display the result. It will remove after the last space from the sentence as shown below.
Step 5
In the Excel sheet, locate the pointer in cell C2 and use the fill handle to drag the cell. Drag cell C2 to cell C7 then it will automatically display the result with the formula as shown below.
Example 2: By Using Ku-tools
Step 1
In the Excel sheet, we have to remove the characters after the first and last spaces from the cells. Locate the pointer and select all the cells that you want to remove the characters after the last space then place the pointer within the ribbon dataset. There are various tabs comprised within the top corner. Place the pointer within the Ku-tools tab and connect to the tab that has countless options encompassed. On the Ku-tools tab, place the pointer and connect to the Merge & Split tab that has the drop-down menu. In the menu, choose and link to the option Split Names tab in the Range & Cells group that will open the dialog box as shown below.
Step 2
In the dialog box, the range is selected then place the pointer and select the First name Split type then click on the OK button. It will open the pop-up window, place the pointer and select any cell to enter in the window then click on the ok button. It will display the removed characters after the last space from cell B2 in the sheet. It will display only the first name in the list as shown below.
Conclusion
Two examples are demonstrated in this article to remove all characters after the first space and last space we want to remove the characters by using the formula and Kutools. The step-by-step explanation is given in both examples which is accurate, and concise.
3K+ Views
