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:
=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.
- Now, we see the FIND function in action.
3. CONCATENATE Function
- Syntax:
The syntax of the CONCATENATE function is:
The arguments can be in any format, e.g. text, number, cell reference, or an array of values. Returns a joint text value combining all the arguments. Syntax: The syntax of the MID function is: Argument: Note: Return Value: Returns a text value consisting of a specific number of characters starting from a specific position of a string. Special Notes: 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. The syntax of the SEARCH function is: Note: Returns the number of characters at which the specific character or text string (find_text) is first found, reading from left to right. 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: The RIGHT function is available from Excel 2007 to the latest version. The SUBSTITUTE function replaces existing text with new text in a text string. Syntax: Argument Explanation: text Required old_text Required new_text Required instance_num Optional Syntax: Arguments: Return Parameter: Returns a specified number of characters from the start of a text string. 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.  Syntax: Argument Explanation: Return Parameter: The CHAR function will return a character based on the number given as an argument. 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. 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. Syntax: Arguments Explanation: Return Parameter: Remove all spaces from a text string except for single spaces between words. 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. Syntax: Arguments Explanation: Return Parameter: Repeated number of the selected text or character in a cell. Syntax: Arguments: text – The text value to convert into a number. Return Parameter: Converts a text string that represents a number to a number. 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. 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=CONCATENATE(text1,[text2],...)
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.
4. MID Function
=MID(text,start_num,num_chars)
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.
5. SEARCH Function
SEARCH(find_text,within_text,[start_num])
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.
6. LEN Function
=LEN(TEXT)
Argument
Required or Optional
Value
text
Required
The text for which to calculate length.
7. RIGHT Function
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.
8. SUBSTITUTE Function
=SUBSTITUTE(text, old_text, new_text, [instance_num])
Argument
Required/Optional
Explanation
The text to change
The text to be replaced
The text to replace old_text with
The text to replace old_text with
9. LEFT Function
=LEFT (text, [num_chars])
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.
10. CLEAN Function
=CLEAN(text)
Argument
Required/Optional
Explanation
text
Required
It is the text string from which you want to remove all of the non-printable characters
11. CHAR Function
=CHAR(number)
Argument
Required/Optional
Explanation
number
Required
A number between 1 to 255 is assigned to a specific character
12. PROPER Function
=PROPER(text)
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.
13. EXACT Function
=
EXACT (text1,text2)
 Arguments
  Required/Optional
 Explanation
             text1
           Required
         First text string
             text1
           Required
         Second text string
14. TRIM Function
=TRIM (text)
Argument
Required/Optional
Explanation
text
Required
The text string from which to eradicate unnecessary spaces
15. CODE Function
=
CODE(text)
Argument
Required/Optional
Explanation
text
Required
For any text string, a code will be returned for the first character of the text string
16. REPT Function
=REPT(text, number_times)
Arguments
Required/Optional
Explanation
text
Required
Text or character that has to be repeated.
number_times
Required
The number of times repetition will occur.
17. VALUE Function
=
VALUE(text)
Things to Remember
Frequently Asked Questions
Conclusions