Top 5 most useful String functions in Excel

In This Walkthrough We

Take a look at some of the most useful text/string functions in Excel

Aimed At:

Who know basic Excel and want to learn about Excel text functions

Prerequisites:

Please read:

String functions in Excel:

Excel offers a wide range of functions for strings to make your work easier. These formulas range from functions that split the text into 2 parts to find a substring within a String. Let’s take a look at some of the most useful functions in Excel.

Left function:

The left function is used to extract the leftmost characters from a String. You can specify the no. of characters you want to extract and Excel will do the rest. Let’s go through this example

Excel Left Function

The LEFT function takes 2 arguments:

– The String or address of the cell that contains that string ( C7 in the above example)

– the no. of characters to extract from the left ( 4 in the above example)

the formula returned “exam” as a result.

Right function:

Just like the Left function, Right function is used to extract the rightmost characters from a String. You can specify the no. of characters you want to extract and Excel will do the rest. Let’s go through this example

Right Function in Excel

The RIGHT function takes 2 arguments:

– The String or address of the cell that contains that string ( C7 in the above example)

– the no. of characters to extract from the right ( 4 in the above example)

the formula returned “text” as a result.

Mid function:

Taking the Left and Right functions one step further, Mid function is used to extract a substring from the middle of a String. You can specify the no. of characters you want to extract and Excel will do the rest. Let’s go through this example

Mid function in Excel

The MID function takes 3 arguments:

– The String or address of the cell that contains that string ( C7 in the above example)

-the location of character to start extracting from ( 3 in the above example, Excel will start extracting from a, the third letter)

– the no. of characters to extract including the character in the 2nd argument  ( in this case 5)

Excel starts extracting from the third letter ( which is a ) and extracts five characters including a, and returns “ample”

Length function:

LEN Function in Excel

Excel allows you to check the no. of characters a String has through LEN function. Let’s take a look

The LEN function takes 1 argument:

– The String or address of the cell that contains that string ( C7 in the above example)

Excel includes space as a character. It counted from e to t and returned 12.

Concatenate function:

Taking the above example a little further, We can combine two or more Cells or Strings through CONCATENATE function. Let’s take a look

Concencate Strings

The CONCATENATE function takes two or more arguments which it combines

In the example above We combined the cell D7(the result of the previous example) with String ” Characters” given as an argument. The result is ” 12 Characters”. Note that the custom String is supplied as an argument in double-quotes.

find function:

find function allows us to find the location of a substring within a string. Let’s take a look.

Find Function in Excel

The Find function takes two arguments ( 3 if you want to specify the start location)

– the substring that you want to find in the String supplied in double-quotes ( in this case “xam”)

– the location of the cell to search within (C7 in this case)

In the example above We searched the C7 cell to find the exact characters “xam”. as the characters start from the second character of the String, Excel returns 2 as a result.

Leave a Comment