LikeOffice.com

excel utility

Keep In Touch:
 contact us  facebook
 
Get HELP with your Excel Project:
 Excel Formula
 List of Excel Formula examples
 
Home >> Excel Functions >> Excel String Functions

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.
Char Left Search
Clean Len Substitute
Code Lower T
Concatenate Mid Text
Dollar Proper Trim
Exact Replace Upper
Find Rept Value
Fixed Right  

Stunning utility for your excel:


Char

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.

See Example

Left

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.


See Example

Search

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.

See Example

Clean

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.

See Example

Len

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.


See Example

Substitute

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.

See Example

Code

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.

See Example

Lower

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.

See Example

T

In Excel, the T function returns the text referred to by a value.
The syntax for the T function is:
T( value )
value is the to extract the text from.

See Example

Concatenate

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"


See Example

Mid

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.

See Example

Text

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.

See Example

Dollar

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.


See Example

Proper

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.

See Example

Trim

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.

See Example

Exact

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.


See Example

Replace

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.

See Example

Upper

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.

See Example

Find

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.

See Example

Rept

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.


See Example

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!.

See Example

Fixed

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.


See Example

Right

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.

See Example




                  

Microsoft® and Microsoft Excel® are registered trademarks of Microsoft Corporation.
©2003-2013 LikeOffice - Privacy Policy - Contact us