Introduction

What are Excel string functions?

Excel string functions allow handling and manipulating text strings: combining text from different cells in one string, extracting parts of the string based on their position or surrounding symbols, substituting parts of the string, etc. These functions are also called text functions, which sometimes creates confusion with the actual TEXT function.

What are the most useful string functions?

There are a lot of string functions that would be helpful in your work. However, we would recommend starting with a few most basic ones before you proceed to learn about others:

  • CONCATENATE – to combine several strings together (alternatively, the & operator can be used)
  • LEFT – to get a substring containing a given number of leftmost characters from the original string
  • RIGHT – to get a substring containing a given number of rightmost characters from the original string
  • LEN – to get the length of the given string (mostly used in combination with LEFT and RIGHT functions)
Quick example
excel string functions example

Figure 1. Excel string functions example

Sample task

Let’s say you need to set up online accounts for a number of your colleagues. You have their IDs, as well as their last and first names, all in separate columns. To set up their accounts, you need to create their unique but easy to remember account names and passwords. Also, you need to fill in the fields with different variations of their names for targeted emails you plan to send in the future:

  • First name, then last name, separated with space (“First Last”)
  • Last name, then first name, separate with a comma (“Last, First”)
  • First name, then the first letter of the last name, separated with space (“First L.”)

Figure 1 shows an example of how this can be done with just a few basic Excel string functions.

How to concatenate Excel strings

First, let’s make the easiest “First Last” option, putting first and last name one after another separated by space.

The easiest way to combine text in Excel is to use the & operator. With this option, you simply list all text strings you want to combine, adding & operator between them:

  1. Start a formula by typing = in your target cell (E4 in our case)
  2. Specify the 1st string you want to combine: select or type D4 (first name)
  3. Type &
  4. Specify the 2nd string you want to combine: type ” “ (single space). Whenever you hardcode any text strings in Excel formula – versus entering them in cells and then referring the cells – you need to inclose them in double quotes.
  5. Type &
  6. Specify the 3rd string you want to combine: select or type C4 (last name)
  7. Press Enter (Figure 2 shows how your screen should look like before this step)

=D4&” “&C4     – the final formula in cell E4

That way, you can combine as many strings as you want.

excel string functions combine strings

Figure 2. Combining Excel strings with & operator

Another way to combine strings is by using Excel CONCATENATE function. In this case, you simply list the strings you want to combine as parameters of the function. Let’s try this approach to create our “Last, First” pattern from the example above:

  1. Start a formula by typing = in your target cell (F4 in our case)
  2. Open CONCATENATE function by typing CONCATENATE(
  3. Specify the 1st string you want to combine: select or type C4 (last name)
  4. Type a comma to move to the next parameter,
  5. Specify the 2nd string you want to combine: type “, “. Again, inclose a hardcoded string in double quotes.
  6. Type a comma: ,
  7. Specify the 3rd string you want to combine: select or type D4 (first name)
  8. Close CONCATENATE function by adding a right bracket: )
  9. Press Enter (Figure 3 shows how your screen should look like before this step)

=CONCATENATE(C4,”, “,D4)    – the final formula in cell F4

excel string functions concatenate

Figure 3. Excel string functions: CONCATENATE

How to use LEFT function in Excel

Next, let’s try combining the first name with the first letter of the last name. To do this, you’ll need to use LEFT function, which extracts a given number of leftmost characters from a string:

  1. Start a formula by typing = in your target cell (G4)
  2. Combine first two strings with the & operator as described above: type D4&” “
  3. Type &
  4. Open LEFT function by typing LEFT(
  5. Specify the string you want to extract from: select or type C4 (last name)
  6. Type a comma to move to the next parameter,
  7. Specify the number of characters to extract from the left: type 1
  8. Close LEFT function by adding a right bracket: )
  9. Press Enter (Figure 4 shows how your screen should look like before this step)

=D4&” “&LEFT(C4,1)&”.”   – the final formula in cell G4 (LEFT function bolded)

excel string functions left

Figure 4. Excel string functions: LEFT

How to use RIGHT function in Excel

To create account names, let’s combine first two letters of the last name with the last 3 digits of the personal ID. In this case, RIGHT function comes in handy, as it extracts a given number of rightmost characters from a string:

  1. Start a formula by typing = in your target cell (H4)
  2. Extract first two characters of the last name with LEFT function: type LEFT(C4,2). See above for explanation of how to use LEFT function
  3. Type &
  4. Open RIGHT function by typing RIGHT(
  5. Specify the string you want to extract from: select or type B4 (ID)
  6. Type a comma to move to the next parameter,
  7. Specify the number of characters to extract from the right: type 3
  8. Close RIGHT function by adding a right bracket: )
  9. Press Enter (Figure 5 shows how your screen should look like before this step)

=LEFT(C4,2)&RIGHT(B4,3)  – the final formula in cell H4 (RIGHT function bolded)

excel string functions right

Figure 5. Excel string functions: RIGHT

How to use Excel LEN function

Finally, for passwords, let’s take the remaining digits of the personal ID. We used last 3 digits in our account names, so that means we need to extract all digits other than the last 3. This is a perfect example of how you would usually nest LEN function inside LEFT or RIGHT functions to refer to the lenght of a given string:

  1. Start a formula by typing = in your target cell (I4)
  2. Open LEFT function and specify the string to extract from: type LEFT(B4
  3. Type a comma to move to the next parameter of LEFT function: ,
  4. Specify the number of characters to extract from the left (lenght of the ID string minus 3):
    1. Open LEN function by typing LEN(
    2. Specify the string which length you’re looking for: select or type B4 (ID)
    3. Close LEN function by adding a right bracket: )
    4. Subtract 3 from the length of the string: type -3
  5. Close LEFT function by adding a right bracket: )
  6. Press Enter (Figure 6 shows how your screen should look like before this step)

=LEFT(B4,LEN(B4)-3)  – the final formula in cell I4 (LEN function bolded)

excel string functions len

Figure 6. Excel string functions: LEN

Test yourself...

String Functions Quiz

Take a quick quiz to understand how well you know Excel string functions:

Share This