Text Functions in Excel

In this tutorial, we will be covering techniques for working with string data in Excel. We will start by discussing how to connect string data from two cells and then move on to the LEFT, RIGHT, LEN, and FIND functions. These functions work with string data by extracting, measuring, and finding them. By the end of this tutorial, you will have a solid understanding of how to work with string data in Excel and be able to efficiently manipulate and analyze your data.

Connecting two cells of strings. Connecting two cells of strings in Excel is a simple process that can be accomplished using the “&” operator. The “&” operator is used to concatenate, or join, two strings together. Here is a step-by-step guide on how to connect two cells of strings using the “&” operator:

1. Select the cell where you want the resulting string to appear. In this example, we will use cell C1.
2. Type in the “=” sign to begin a formula.
3. Select the first cell that contains the string you want to use. In this example, we will use cell A1.
4. Type in the “&” operator.
5. Type in a space within quotation marks, this is to separate the two cells with a space.
6. Type in another “&” operator.
7. Select the second cell that contains the string you want to use. In this example, we will use cell B1.
8. Press enter to complete the formula. The resulting formula should be =A1&” “&B1

The resulting string in cell C1 will now be the contents of cells A1 and B1 joined together with a space between them.

You can also use this method to connect cells of strings with any separator you prefer. for example, to connect two cells with a comma separator, you can use the formula =A2&”,”&B2.

Extracting characters from the start of the string. In this example, we want to extract characters from the beginning of a string until the nth character. To do this, we will use the LEFT function. The LEFT function in Excel is used to extract a specific number of characters from the beginning of a string. Here is a step-by-step guide on how to use the LEFT function in Excel:

1. Select the cell where you want the resulting string to appear. In this example, we will use cell C1.
2. Type in the “=” sign to begin a formula.
3. Type in the word “LEFT” followed by an open parenthesis. “(“
4. Select the cell that contains the string you want to extract from. In this example, we will use cell A2 that contains Joseph Dzousa.
5. Type in a comma.
6. Type in the number of characters you want to extract from the beginning of the string. In this example, we will extract the first 6 characters to extract the name: ‘Joseph’.
7. Type in a close parenthesis. The resulting formula should be =LEFT(A2,6)
8. Press enter to complete the formula.

Extracting characters from the end of the string. Using the same example, we want to extract characters from the end of the string until the nth character. To do this, we will use the RIGHT function. The RIGHT function in Excel is used to extract a specific number of characters from the end of a string. Here is a step-by-step guide on how to use the RIGHT function in Excel:

1. Select the cell where you want the resulting string to appear. In this example, we will use cell C1.
2. Type in the “=” sign to begin a formula.
3. Type in the word “RIGHT” followed by an open parenthesis. “ (“
4. Select the cell that contains the string you want to extract from. In this example, we will use cell A2 that contains Joseph Dzousa.
5. Type in a comma.
6. Type in the number of characters you want to extract from the end of the string. In this example, we will extract the last 6 characters to extract the name: ‘Dzousa’.
7. Type in a close parenthesis. The resulting formula should be =RIGHT(A2,6)
8. Press enter to complete the formula.

Extracting characters from the middle of the string. With the same example, we would like to extract characters from the nth character to the nth character. To do this, we will use the MID function. The MID function in Excel is used to extract a specific number of characters from a specific location within a string. . Here is a step-by-step guide on how to use the RIGHT function in Excel:

1. Select the cell where you want the resulting string to appear. In this example, we will use cell C1.
2. Type in the “=” sign to begin a formula.
3. Type in the word “MID” followed by an open parenthesis.
4. Select the cell that contains the string you want to extract from. In this example, we will use cell A1.
5. Type in a comma.
6. Type in the starting position of the characters you want to extract. In this example, we will start at the 4th character.
7. Type in another comma.
8. Type in the number of characters you want to extract. In this example, we will extract 6characters.
9. Type in a close parenthesis. The resulting formula should be =MID(A1,4,6)
10. Press enter to complete the formula.

Note: Excel counts spaces as characters.

Finding how many characters there are in a string. In this example, we want to figure out how many characters there are in the string. To do this, we want to use the LEN function. The LEN function in Excel is used to measure the length of a string. Here is a step-by-step guide on how to use the LEN function in Excel:

1. Select the cell where you want the resulting number to appear. In this example, we will use cell C1.
2. Type in the “=” sign to begin a formula.
3. Type in the word “LEN” followed by an open parenthesis.
4. Select the cell that contains the string you want to measure. In this example, we will use cell A1.
5. Type in a close parenthesis. The resulting formula should be =LEN(A1)
6. Press enter to complete the formula.

Finding where a character/set of characters are located in a cell. In the same example, we want to figure out where the character “Dzousa” specifically starts in the string. To do this, we will use the FIND function.The FIND function in Excel is used to locate the position of a specific character or substring within a string. Here is a step-by-step guide on how to use the FIND function in Excel:

Select the cell where you want the resulting number to appear. In this example, we will use cell C1.

1. Type in the “=” sign to begin a formula.
2. Type in the word “FIND” followed by an open parenthesis.
3. Type in the character or substring you want to find within quotation marks. In this example, we will find the substring “Dzousa”.
4. Type in a comma.
5. Select the cell that contains the string you want to search. In this example, we will use cell A2..
6. Type in another comma and the starting position for the search. (Optional)
7. Type in a close parenthesis. The resulting formula should be =FIND(“Dzousa”,A2)
8. Press enter to complete the formula.

Note: If the substring is not found, the function returns a #VALUE! Error.

Replacing a character/set of characters in a string. In the same example, we want to replace the word “Dzousa” with “Kimmich.” To do this, we will use the SUBSTITUTE function. This function is useful when you need to make changes to a large amount of data, such as replacing a specific word or phrase in a cell. Here is a step-by-step guide on how to use the SUBSTITUTE function in Excel:

1. Select the cell where you want the resulting string to appear. In this example, we will use cell C1.
2. Type in the “=” sign to begin a formula.
3. Type in the word “SUBSTITUTE” followed by an open parenthesis.
4. Select the cell that contains the string you want to change. In this example, we will use cell A2.
5. Type in a comma.
6. Type in the character or substring you want to replace within quotation marks. In this example, we will replace the word “Dzousa”
7. Type in a comma.
8. Type in the new character or substring you want to use within quotation marks. In this example, we will use “Kimmich.”
9. Type in a close parenthesis. The resulting formula should be =SUBSTITUTE(A2,”Dzousa”,”Kimmich”)
10. Press enter to complete the formula.

Conclusion

Excel offers a variety of functions for working with string data. The LEFT, RIGHT, MID, LEN, and FIND functions allow you to extract specific parts of a string, measure the length of a string, and find the location of a specific character or substring within a string. The & operator is used to connect string data from two cells. And the SUBSTITUTE function allows you to replace a specific character or substring within a string with a new character or substring. By understanding and utilizing these functions, you will be able to effectively manipulate and analyze your string data in Excel.