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

Get FREE Advanced Excel Exercises with Solutions!

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

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

### 1. TEXT Function

• Syntax:

`=TEXT(value, format_text)`

• Arguments Explanation:
Argument Required/Optional Explanation
value Required Value in a numeric form that has to be formatted.
format_text Required Specified number format.
• Return Parameter:

A numeric value in a specified format.

### 2. FIND Function

• Syntax:

`=FIND(find_text, within_text, [start_num])`

• Arguments Explanation:
Argument Required/Optional Explanation
find_text Required A text or a part of a text to be searched for in a cell containing another text string.
within_text Required The cell containing the text where the defined character or part of the text will be searched for.
[start_num] Optional Defined position in the text string from where the character count will be initiated.
• Return Parameter:

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

### 3. CONCATENATE Function

• Syntax:

The syntax of the CONCATENATE function is:

`=CONCATENATE(text1,[text2],...)`

• Arguments:

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

Argument Required/Optional Value
text1 Required The first value to be joined. Can be any text value, number, cell reference, or array of values.
[text2] Optional The second value is to be joined. Can be any text value, number, cell reference, or array of values.
• Return Value:

Returns a joint text value combining all the arguments.

### 4. MID Function

Syntax:

The syntax of the MID function is:

`=MID(text,start_num,num_chars)`

Argument:

Argument Required/Optional Explanation
text Required The string from which characters will be extracted. It can be any text value, number, or array.
start_num Required The starting position from which characters will be extracted. It can be a single number or an array of numbers.
num_chars Required The total number of characters that will be extracted. Can be a single number or an array of numbers.

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:

`SEARCH(find_text,within_text,[start_num])`

• Arguments:
ARGUMENT REQUIREMENT EXPLANATION
find_text Required The text that is searched for. Can be a single text or an array of texts.
within_text Required The text value within which the find_text argument is searched for. Can be a single text value or an array of text values.
[start_num] Optional The position of the within_text argument from which it starts searching. Can be a single number or an array of numbers. Default is 1.

Note:

• All three arguments can be either a single value or an array of values.
• In the case that at least one of the arguments is an array, the formula will turn into an Array Formula and you have to press Ctrl + Shift + Enter to enter the formula.
• The third argument [start_num] is optional. The default is 1.
• 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:

`=LEN(TEXT)`

Arguments:

Argument Required or Optional Value
text Required The text for which to calculate length.

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:

=RIGHT(text,[num_chars])

• Arguments Explanation:
Arguments Required/Optional Explanation
text Required Pass the text from which to extract characters on the right.
[num_chars] Optional Pass the number of characters to extract, starting on the right. The default value is 1.
• Version:

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

Notes
• If num_chars is not provided, it defaults to 1.
• If num_chars is greater than the number of characters available, the RIGHT function returns the entire text string.
• RIGHT will extract digits from numbers as well as text.
• This function does not consider the formatting of any cell, like a date, currency, etc.

### 8. SUBSTITUTE Function

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

Syntax:

`=SUBSTITUTE(text, old_text, new_text, [instance_num])`

Argument Explanation:

Argument Required/Optional Explanation

text

Required

The text to change

old_text

Required

The text to be replaced

new_text

Required

The text to replace old_text with

instance_num

Optional

The text to replace old_text with
• Finally, we see the SUBSTITUTE function in action.

### 9. LEFT Function

Syntax:

`=LEFT (text, [num_chars])`

Arguments:

Argument Required/Optional Description
text Required The text string that contains the characters to extract.
num_chars Optional The number of characters to extract. By default 1.

Return Parameter:

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

### 10. CLEAN Function

Syntax:

`=CLEAN(text)`

Arguments:

 Argument Required/Optional Explanation text Required It is the text string from which you want to remove all of the non-printable characters

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.

### 11. CHAR Function

Â Syntax:

`=CHAR(number)`

Argument Explanation:

Argument Required/Optional Explanation
number Required A number between 1 to 255 is assigned to a specific character

Return Parameter:

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

### 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:

`=PROPER(text)`

Arguments:

Argument Required/Optional Explanation
text Required The text should be converted to a proper case. The text could, however, be a formula that yields text, text that is surrounded in quotation marks, or perhaps a reference to such a cell that includes the text.

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:

`=``EXACT (text1,text2)`

Arguments Explanation:

Â  Arguments Â  Â Required/Optional Â Explanation
Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â text1 Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Required Â  Â  Â  Â  Â  Â  Â  Â  Â  First text string
Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â text1 Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Required Â  Â  Â  Â  Â  Â  Â  Â  Â Second text string

Return Parameter:

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

### 14. TRIM Function

Syntax:

`=TRIM (text)`

Arguments Explanation:

Argument Required/Optional Explanation
text Required The text string from which to eradicate unnecessary spaces

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:

`=``CODE(text)`

Argument Explanation:

Argument Required/Optional Explanation
text Required For any text string, a code will be returned for the first character of the text string

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:

`=REPT(text, number_times)`

Arguments Explanation:

Arguments Required/Optional Explanation
text Required Text or character that has to be repeated.
number_times Required The number of times repetition will occur.

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:

`=``VALUE(text)`

Arguments:

text â€“ The text value to convert into a number.

Return Parameter:

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

## 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

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

## What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Joyanta Mitra

Joyanta Mitra, a BSc graduate in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology, has dedicated over a year to the ExcelDemy project. Specializing in programming, he has authored and modified 60 articles, predominantly focusing on Power Query and VBA (Visual Basic for Applications). His expertise in VBA programming is evident through the substantial body of work he has contributed, showcasing a deep understanding of Excel automation, and enhancing the ExcelDemy project's resources with valuable... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF