If you spend time on your Excel Project and you feel it can be done some other way, you may contact our Excel Help Team to help you with your Excel Project or use our Excel Question page.
Excel String Functions
Excel's Text and String Functions help you manage the text data in your spreadsheets.
In Excel, the Char function returns the character based on the NUMBER code.
The syntax for the Char function is:
Char( number_code )
number_code is the NUMBER used to retrieve the character.
In Excel, the Left function allows you to extract a substring from a string, starting from the left-most character.
The syntax for the Left function is:
Left( text, number_of_characters )
text is the string that you wish to extract from.
number_of_characters indicates the number of characters that you wish to extract starting from the left-most character.
In Excel, the Search function returns the location of a substring in a string.
The search is NOT case-sensitive.
The syntax for the Search function is:
Search( text1, text2, start_position )
text1 is the substring to search for in text2.
text2 is the string to search.
start_position is the position in text1 where the search will start.
The first position is 1.
If the Search function does not find a match, it will return a #VALUE! error.
In Excel, the Clean function removes all nonprintable characters from a string.
The syntax for the Clean function is:
Clean( text )
text is the value that has all nonprintable characters removed from.
In Excel, the Len function returns the length of the specified string.
The syntax for the Len function is:
Len( text )
text is the string to return the length for.
In Excel, the Substitute function replaces a set of characters with another.
The syntax for the Substitute function is:
Substitute( text, old_text, new_text, nth_appearance )
text is the original string to use to perform the substitution.
old_text is the existing characters to replace.
new_text is the new characters to replace old_text with.
nth_appearance is optional.
It is the nth appearance of old_text that you wish to replace.
I f this parameter is omitted, then every occurrence of old_text will be replaced with new_text.
In Excel, the Code function returns the NUMBER code that represents the specific character.
The syntax for the Code function is:
Code( text )
text is the specified character to retrieve the NUMBER code for.
If more than one character is entered, the function will return the value for the first character and ignore all of the characters after the first.
In Excel, the Lower function converts all letters in the specified string to lowercase.
If there are characters in the string that are not letters, they are unaffected by this function.
The syntax for the Lower function is:
Lower( text )
text is the string to convert to lowercase.
In Excel, the Concatenate function allows you to join 2 or more strings together.
The syntax for the Concatenate function is:
Concatenate( text1, text2, ...
text_n )
There can be up to 30 strings that are joined together.
To concatenate multiple strings into a single string in Excel, you can also use the "&" operator to separate the string values.
string_1 & string_2 & string_n
VBA Code
The "&" operator can be used to concatenate strings in VBA code.
For example:
LValue = "Alpha" & "bet"
In Excel, the Mid function extracts a substring from a string (starting at any position).
The syntax for the Mid function is:
Mid( text, start_position, number_of_characters )
text is the string that you wish to extract from.
start_position indicates the position in the string that you will begin extracting from.
The first position in the string is 1.
number_of_characters indicates the number of characters that you wish to extract.
In Excel, the Text function returns a value converted to text with a specified format.
The syntax for the Text function is:
Text( value, format )
value is the value to convert to text.
format is the format to display the results in.
In Excel, the Dollar function converts a number to text, using a currency format.
The format used is $#,##0.00_);($#,##0.00).
The syntax for the Dollar function is:
Dollar( number, decimal_places )
number is the number to convert to text.
decimal_places is the number of decimal places to display.
The number will be rounded accordingly.
In Excel, the Proper function sets the first character in each word to uppercase and the rest to lowercase.
The syntax for the Proper function is:
Proper( text )
text is the string argument whose first character in each word will be converted to uppercase and all remaining characters converted to lowercase.
In Excel, the Trim function returns a text value with the leading and trailing spaces removed.
The syntax for the Trim function is:
Trim( text )
text is the text value to remove the leading and trailing spaces from.
In Excel, the Exact function compares two strings and returns TRUE if both values are the same.
Otherwise, it will return FALSE.
The syntax for the Exact function is:
Exact( text1, text2 )
text1 and text2 are the values to compare.
The Exact function is case-sensitive.
In Excel, the Replace function replaces a sequence of characters in a string with another set of characters.
The syntax for the Replace function is:
Replace( old_text, start, number_of_chars, new_text )
old_text is the original string value.
start is the position in old_text to begin replacing characters.
number_of_chars is the number of characters to replace in old_text.
new_text is the replacement set of characters.
In Excel, the Upper function allows you to convert text to all uppercase.
The syntax for the Upper function is:
Upper( text )
text is the string that you wish to convert to uppercase.
In Excel, the Find function returns the location of a substring in a string.
The search is case-sensitive.
The syntax for the Find function is:
Find( text1, text2, start_position )
text1 is the substring to search for in text2.
text2 is the string to search.
start_position is the position in text1 where the search will start.
The first position is 1.
If the Find function does not find a match, it will return a #VALUE! error.
In Excel, the Rept function returns a repeated text value a specified number of times.
The syntax for the Rept function is:
Rept( text, number )
text is the text value to repeat.
number is the number of times to repeat the text value.
In Excel, the Value function converts a text value that represents a number to a number.
The syntax for the Value function is:
Value( text )
text is the text value to convert to a number.
If text is not a number, the Value function will return #VALUE!.
In Excel, the Fixed function returns a text representation of a number rounded to a specified number of decimal places.
The syntax for the Fixed function is:
Fixed( number, decimal_places, no_commas )
number is the number to round.
decimal_places is the number of decimal places to display in the result.
no_commas - If this parameter is set to TRUE, the result will not display commas.
If it is set to FALSE, it will display commas in the result.
In Excel, the Right function extracts a substring from a string starting from the right-most character.
The syntax for the Right function is:
Right( text, number_of_characters )
text is the string that you wish to extract from.
number_of_characters indicates the number of characters that you wish to extract starting from the right-most character.
See Example
In Excel, the Right function extracts a substring from a string starting from the right-most character.
The syntax for the Right function is:
Right( text, number_of_characters )
text is the string that you wish to extract from.
number_of_characters indicates the number of characters that you wish to extract starting from the right-most character.
