How to Use LEN to Delete Text in Excel
If you are cleaning your data set and want to trim text, there are a number of ways to do so. The way you choose to delete text depends largely on how complex your data set is, whether your text is all of the same length and whether you are deleting the same text or different text each time. Use the LEN function to delete text when all your text is of the same length. By itself, the LEN function only counts the number of characters in your text string, but by combining it with other Excel functions, you can use it to delete text.
Other People Are Reading
Instructions
-
-
1
Click an empty cell in your Excel worksheet.
-
2
Enter an equal sign followed by either the LEFT or RIGHT function and an open parenthesis. The LEFT function counts and extracts the number of characters you specify starting from the left side of your text string. The RIGHT function does the same, starting from the right side. Therefore, if you want to delete text at the end of your string, you should use LEFT and if you want to delete text from the beginning of your string, you should use RIGHT. For example, if you have a list of ID numbers prefaced by the word "ID," such as "ID 23049" and you want to leave just the number and delete the word "ID," you should use the RIGHT string:
=RIGHT(
-
-
3
Enter the cell number for the text string you want to delete, followed by a comma. For example:
=RIGHT(B4,
-
4
Enter the LEN function at this point to replace the second argument in the LEFT or RIGHT function. Enter "LEN" followed by the same cell reference in parentheses, followed by a minus sign and the number of characters you want to subtract. Close the formula with a parenthesis. For example, if you want to delete the word "ID" from "ID 23049" leaving just the number, you would want to subtract the first three characters from the total number of characters. In this example, the total number of characters is eight and you subtract three from the length of the string:
=RIGHT(B4,LEN(B4)-3)
-
5
Press "Enter" to see the result. You should see the trimmed text in the current cell.
-
1
