Monthly Archives: June 2019

Count Number of Words in a Cell

Number of Words This example describes how to count the number of words in a cell. 1a. The TRIM function returns a string with leading spaces, extra spaces and trailing spaces removed. 1b. To get the length of the string with normal spaces, we combine the LEN and TRIM function.   2a. The SUBSTITUTE function replaces existing text… Read More »

Count number of instances in a Text

Number of Instances This example describes how to count the number of instances of text (or a number) in a cell. 1. Use the LEN function to get the length of the string (25 characters, including spaces). 2. The SUBSTITUTE function replaces existing text with new text in a string. LEN(SUBSTITUTE(A1,B1,””)) equals 13 (the length of… Read More »

How to Separate Strings

Separate Strings This example teaches you how to separate strings in Excel. The problem we are dealing with is that we need to tell Excel where we want to separate the string. In case of Smith, Mike the comma is at position 6 while in case of Williams, Janet the comma is at position 9. 1. To get… Read More »

Text Functions in Excel

Text Functions in Excel Join Strings | Left | Right | Mid | Len | Find | Substitute Excel has many functions to offer when it comes to manipulating text strings. Join Strings To join strings, use the & operator. Note: to insert a space, use ” ” Left To extract the leftmost characters from a string,… Read More »

Formula Errors

Formula Errors ##### error | #NAME? error | #VALUE! error | #DIV/0! error | #REF! error | # IfError | # IsError | Aggregate | Circular reference | Floating Point Errors | This chapter teaches you how to deal with some common formula errors in Excel. ##### error When your cell contains this error code, the column isn’t wide… Read More »