Excel String Functions [Basics + Practical Example of 17 Functions]

In this article, we will explain 17 Excel string functions with all sorts of examples of every single function.

Download Practice Workbook

Excel String Functions: 17 Functions with Basic Things and Practical Examples

1. TEXT Function

• Syntax:

• Arguments Explanation:

• Return Parameter:

A numeric value in a specified format.

• Now we see the use of the TEXT function.

2. FIND Function

• Syntax:

• Arguments Explanation:

• Return Parameter:

A numerical value in an integer form that defines the position of the defined character in a text string.

• Now, we see the FIND function in action.

3. CONCATENATE Function

• Syntax:

The syntax of the CONCATENATE function is:

• Arguments:

The arguments can be in any format, e.g. text, number, cell reference, or an array of values.

• Return Value:

Returns a joint text value combining all the arguments.

• Now we see the CONCATENATE function in action.

4. MID Function

Syntax:

The syntax of the MID function is:

Argument:

Note:

• The first argument text can be any text value, number, or array of text values or numbers. But whether it is a text value or a number, the return value will always be a text value.
• The next two arguments start_num and num_chars can be any number or an array of numbers.
• If you use an array argument, the formula will be an Array Formula and you have to press Ctrl + Shift + Enter.

Return Value:

Returns a text value consisting of a specific number of characters starting from a specific position of a string.

Special Notes:

• If the start_num argument is greater than the total number of characters of a string, the MID function will return an empty string.
• Now, we will see the MID function in action.

5. SEARCH Function

The SEARCH function returns the number of characters after finding a specific character or text string, reading from left to right. This function Searches for a case-insensitive match. It works for both Array and Non-Array Formula and is available from Excel 2003.

• Syntax:

The syntax of the SEARCH function is:

• Arguments:

• Return Value

Returns the number of characters at which the specific character or text string (find_text) is first found, reading from left to right.

• Finally, we get the dataset showing how the SEARCH function works.

6. LEN Function

The LEN function in Excel is a function that returns the length of a given string. The function is useful for a variety of purposes, including for finding the number of characters in a given cell or range of cells, or for finding the number of characters in a given string of text.

Syntax:

The LEN function is described with the following syntax:

Arguments:

Note:

• LEN reflects the length of text as a number.
• This function works with numbers, but number formatting is not included.
• LEN function returns zero in terms of empty cells.
• Now we will see the LEN function in action.

7. RIGHT Function

• Syntax:

• Arguments Explanation:

• Version:

The RIGHT function is available from Excel 2007 to the latest version.

• Finally, we see every instance of using the RIGHT function.

8. SUBSTITUTE Function

The SUBSTITUTE function replaces existing text with new text in a text string.

Syntax:

Argument Explanation:

• Finally, we see the SUBSTITUTE function in action.

9. LEFT Function

Syntax:

Arguments:

Return Parameter:

Returns a specified number of characters from the start of a text string.

• Now, we see the different instances of using the LEFT function.

10. CLEAN Function

Syntax:

Arguments:

Return Value:

After Entering the function, it will give you the text string free from all of the non-printable characters.

Note:

The CLEAN function can only remove the non-printable characters represented by numbers 0 to 31 in the 7-bit ASCII code.

• Finally, we see the various instances of using the CLEAN function.

11. CHAR Function

 Syntax:

Argument Explanation:

Return Parameter:

The CHAR function will return a character based on the number given as an argument.

• Now, we see the output of the CHAR function in different instances.

12. PROPER Function

Usually, the PROPER function converts a text string into the proper case; the first letter in each word to uppercase, and all other letters to lowercase.

Syntax:

Arguments:

Returning Parameter:

It returns the first letter of every word to uppercase and other letters to lowercase.

• Now, we finally see the PROPER function in action.

13. EXACT Function

The EXACT function compares two texts and then returns TRUE (in case the texts are exactly the same) or FALSE (in case the texts are exactly not the same).

Syntax:

Arguments Explanation:

Return Parameter:

TRUE or FALSE, depending on the exact match between the two arguments.

• Now, let’s see the EXACT function in action.

14. TRIM Function

Syntax:

Arguments Explanation:

Return Parameter:

Remove all spaces from a text string except for single spaces between words.

• Now, let’s delve into the matter of using the TRIM function in Excel.

15. CODE Function

Syntax:

Argument Explanation:

Return Parameter:

A numeric number will be returned for the first character of the input text string. In general, the number returned by CODE represents the ASCII decimal code for a character. The CODE function was developed to operate in an ASCII/ANSI domain, and it only knows how to map characters to integers 0-255.

• Now, we dive into the matter of using the CODE function in Excel.

16. REPT Function

Syntax:

Arguments Explanation:

Return Parameter:

Repeated number of the selected text or character in a cell.

• Now let’s see the use of the different instances of the REPT function in Excel.

17. VALUE Function

Syntax:

Arguments:

text – The text value to convert into a number.

Return Parameter:

Converts a text string that represents a number to a number.

• Now, we see the use of the VALUE function in Excel.

Things to Remember

• Data Truncation: When using functions like LEFT, RIGHT, or MID to extract a portion of a text string, there’s a risk of data truncation if the specified length exceeds the actual length of the text. This can result in missing or incomplete information.
• Case Sensitivity Issues: Excel String Functions are generally case-sensitive. If there’s a mismatch in the case of the text being compared or manipulated, the results may not be as expected, leading to errors or incorrect outputs.
• Leading and Trailing Spaces: Extra leading or trailing spaces in text strings can cause discrepancies in search, comparison, and data manipulation operations. Functions like TRIM can be used to address this, but if overlooked, it can lead to issues

Frequently Asked Questions

1. Are Excel String Functions case-sensitive?

Yes, most Excel String Functions are case-sensitive, meaning they distinguish between uppercase and lowercase characters in text comparisons.

2. Why is the TEXT function not working properly?

The TEXT function needs quotation marks and the implementation of the accurate formula.

3. What will happen if I put an asterisk(*) in the TEXT argument?

The argument of the TEXT function does not include an asterisk and so it will produce an error halting the successful completion of the formula execution.

Conclusions

In summary, Excel String Functions are indispensable tools for text manipulation and data processing in Microsoft Excel. They enable users to extract, search, concatenate, and modify text strings, improving data analysis and presentation. However, users should be cautious of potential faults, such as data truncation and case sensitivity issues, to ensure accurate results. With practice and understanding, these functions can significantly enhance productivity and open up new possibilities for data management and analysis in Excel.

<< Go Back to Excel Function Categories | Excel Functions | Learn Excel