Author Archives: Krishna Manda

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 with… 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

Text Functions 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, use the… 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 »

Lookup & Reference (Index & Match) Functions

Lookup & Reference functions help you to work with arrays of data, and are particularly useful when you need to cross reference between different data sets. They perform tasks such as providing information about a range, returning the location of a given address or value, or looking up specific Vlookup The VLOOKUP (Vertical lookup) function… Read More »