While working on excel with bulk data we may have a situation:
- Delete first or the last word from a cell in excel and
- Find the total number of words in a cell.
1. How To Delete A Word From A Cell Containing Data In An Excel Cell?
Introduction: Sometimes we have bulk data in Excel from which we have to Delete last or first word from a particular cell containing the data.
In an Excel sheet Select the blank Cell where you want the data to appear after deletion of the desired word and type the formula as:
Excel Formula:
=LEFT(TRIM(A2),FIND("~",SUBSTITUTE(A2," ","~",LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))))-1)
For Example: Let’s take the following screenshot of an excel sheet in which Cell A2 contains the data from which you wish to delete the Last Word and C2 is the cell where you wish the result to appear. In the selected cell C2 type the formula =LEFT(TRIM(A2),FIND(“~”,SUBSTITUTE(A2,” “,”~”,LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2),” “,””))))-1) and press enter to get the desired result:
Note: In the given formula “LEFT” deletes the last word. For deleting the first word put “RIGHT” in the formula.
For example, see the below screenshot:
2. How To Count Total Words In A Cell In Excel Formula?
Introduction: Many times we have bulk data in Excel from which we have to extract the total number of words contained by a particular cell.
In an Excel sheet Select the blank Cell where you want a total count of words of the particular cell to appear and type the formula as:
Excel Formula:
=LEN(TRIM(A2))-LEN(SUBSTITUTE(A2," ",""))+1
For Example: Let’s take the following screenshot of an excel sheet in which Cell A2 contains the data and you wish the total number of words to appear in the Cell D2. In the selected cell D2 type the formula =LEN(TRIM(A2))-LEN(SUBSTITUTE(A2,” “,”))+1 and press enter to get the desired total count of words contained by the cell A2.
For a total count of words in the other Cells of A3, A4, A5 and so on select the range of cells starting from D2 where you have the formula and press “Ctrl +D”, This syntax will be applied on all selected cell.
________________
