Text functions

Click one of the links in the following list to see detailed help about the function.

See Also

• List of worksheet functions (alphabetical)
• List of worksheet functions (by category)

Excel > Function reference > Text and data

ASC function

For Double-byte character set (DBCS) languages, changes full-width (double-byte) characters to half-width (single-byte) characters.

Syntax

ASC(text)

Text   is the text or a reference to a cell that contains the text you want to change. If text does not contain any full-width letters, text is not changed.

Examples

=ASC("EXCEL")

equals "EXCEL"

=ASC("

")

equals " "

See Also

• Text functions

Excel > Function reference > Text and data

BAHTTEXT function

Excel 2007

Converts a number to Thai text and adds a suffix of "Baht."

In Excel for Windows, you can change the Baht format to a different style from the Control Panel by using Regional and Language Options in Windows Vista or in Microsoft Windows XP.

In Excel for the Macintosh, you can change the Baht number format to a different style by using Control Panel for Numbers.

Syntax

BAHTTEXT(number)

Number   is a number you want to convert to text, or a reference to a cell containing a number, or a formula that evaluates to a number.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• Text functions

Excel > Function reference > Text and data

CHAR function

Excel 2007

Returns the character specified by a number. Use CHAR to translate code page numbers you might get from files on other types of computers into characters.

Syntax

CHAR(number)

Number   is a number between 1 and 255 specifying which character you want. The character is from the character set used by your computer.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• CODE function
• Combine two or more columns by using a function
• Text functions

Excel > Function reference > Text and data

CLEAN function

Excel 2007

Removes all nonprintable characters from text. Use CLEAN on text imported from other applications that contains characters that may not print with your operating system. For example, you can use CLEAN to remove some low-level computer code that is frequently at the beginning and end of data files and cannot be printed.

Important  The CLEAN function was designed to remove the first 32 nonprinting characters in the 7-bit ASCII code (values 0 through 31) from text. In the Unicode character set (Unicode: A character encoding standard developed by the Unicode Consortium. By using more than one byte to represent each character, Unicode enables almost all of the written languages in the world to be represented by using a single character set.), there are additional nonprinting characters (values 127, 129, 141, 143, 144, and 157). By itself, the CLEAN function does not remove these additional nonprinting characters. For an example of how to remove these additional nonprinting characters from text, see Remove spaces and nonprinting characters from text.

Syntax

CLEAN(text)

Text   is any worksheet information from which you want to remove nonprintable characters.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• CHAR function
• Remove spaces and nonprinting characters from text
• Text functions
• TRIM function

Excel > Function reference > Text and data

CODE function

Excel 2007

Returns a numeric code for the first character in a text string. The returned code corresponds to the character set used by your computer.

Syntax

CODE(text)

Text   is the text for which you want the code of the first character.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• CHAR function
• Text functions

Excel > Function reference > Text and data

CONCATENATE function

Excel 2007

Tags  combine; combine text; connect; functions; join; mail merge; merge; text

What are tags?

This article describes the formula syntax and usage of the CONCATENATE function (function: A prewritten formula that takes a value or values, performs an operation, and returns a value or values. Use functions to simplify and shorten formulas on a worksheet, especially those that perform lengthy or complex calculations.) in Microsoft Office Excel.

Description

The CONCATENATE function joins up to 255 text strings into one text string. The joined items can be text, numbers, cell references, or a combination of those items. For example, if your worksheet contains a person's first name in cell A1 and the person's last name in cell B1, you can combine the two values in another cell by using the following formula:

=CONCATENATE(A1," ",B1)

The second argument in this example (" ") is a space character. You must specify any spaces or punctuation that you want to appear in the results as an argument that is enclosed in quotation marks.

Syntax

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

The CONCATENATE function syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):

• text1  Required. The first text item to be concatenated.
• text2 ...  Optional. Additional text items, up to a maximum of 255 items. The items must be separated by commas.

 Note    You can also use the ampersand (&) calculation operator instead of the CONCATENATE function to join text items. For example, =A1 & B1 returns the same value as =CONCATENATE(A1, B1).

Example

The example may be easier to understand if you copy it to a blank worksheet.

How do I copy an example?

1. Select the example in this article.

Important  Do not select the row or column headers.

Selecting an example from Help

2. Press CTRL+C.
3. In Excel, create a blank workbook or worksheet.
4. In the worksheet, select cell A1, and press CTRL+V.

Important  For the example to work properly, you must paste it into cell A1 of the worksheet.

5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

Related Office Online discussions

Read related questions and answers from other Microsoft Office customers.

• How do i combine a date cell and a time cell in excel?
• How do I merge two cells without deleting data from the cell?
• How do I combine text and a calculation in the same field?
• How do i combine a lookup and a sumif formula?

See Also

• Combine first and last names
• Combine text and numbers
• Combine two or more columns by using a function
• Display only the last four digits of identification numbers
• Text functions

Excel > Function reference > Text and data

DOLLAR function

Excel 2007

The function described in this Help topic converts a number to text format and applies a currency symbol. The name of the function (and the symbol that it applies) depends upon your language settings.

This function converts a number to text using currency format, with the decimals rounded to the specified place. The format used is $#,##0.00_);($#,##0.00).

Syntax

DOLLAR(number,decimals)

Number   is a number, a reference to a cell containing a number, or a formula that evaluates to a number.

Decimals   is the number of digits to the right of the decimal point. If decimals is negative, number is rounded to the left of the decimal point. If you omit decimals, it is assumed to be 2.

Remark

The major difference between formatting a cell that contains a number by using a command (On the Home tab, in the Number group, click the arrow next to Number, and then click Number.) and formatting a number directly with the DOLLAR function is that DOLLAR converts its result to text. A number formatted with the Format Cells dialog box is still a number. You can continue to use numbers formatted with DOLLAR in formulas, because Microsoft Excel converts numbers entered as text values to numbers when it calculates.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• FIXED function
• TEXT function
• Text functions
• VALUE function

Excel > Function reference > Text and data

EXACT function

Excel 2007

Compares two text strings and returns TRUE if they are exactly the same, FALSE otherwise. EXACT is case-sensitive but ignores formatting differences. Use EXACT to test text being entered into a document.

Syntax

EXACT(text1,text2)

Text1   is the first text string.

Text2   is the second text string.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• Check if a cell contains text (case-sensitive)
• DELTA function
• LEN, LENB functions
• SEARCH, SEARCHB functions
• Text functions

Excel > Function reference > Text and data

FIND, FINDB functions

Excel 2007

FIND and FINDB locate one text string within a second text string, and return the number of the starting position of the first text string from the first character of the second text string.

Important  FIND is intended for use with languages that use the single-byte character set (SBCS), whereas FINDB is intended for use with languages that use the double-byte character set (DBCS). The default language setting on your computer affects the return value in the following way:

• FIND always counts each character, whether single-byte or double-byte, as 1, no matter what the default language setting is.
• FINDB counts each double-byte character as 2 when you have enabled the editing of a language that supports DBCS and then set it as the default language. Otherwise, FINDB counts each character as 1.

The languages that support DBCS include Japanese, Chinese (Simplified), Chinese (Traditional), and Korean.

For more information, see the following Help topics:

• Enable Windows XP support for certain languages (East Asian, Southeast Asian, Indic, and right-to-left languages)
• Enable entering text in another language in Microsoft Office

Syntax

FIND(find_text,within_text,start_num)

FINDB(find_text,within_text,start_num)

Find_text   is the text you want to find.

Within_text   is the text containing the text you want to find.

Start_num   specifies the character at which to start the search. The first character in within_text is character number 1. If you omit start_num, it is assumed to be 1.

Remarks

• FIND and FINDB are case sensitive and don't allow wildcard characters. If you don't want to do a case sensitive search or use wildcard characters, you can use SEARCH and SEARCHB.
• If find_text is "" (empty text), FIND matches the first character in the search string (that is, the character numbered start_num or 1).
• Find_text cannot contain any wildcard characters.
• If find_text does not appear in within_text, FIND and FINDB return the #VALUE! error value.
• If start_num is not greater than zero, FIND and FINDB return the #VALUE! error value.
• If start_num is greater than the length of within_text, FIND and FINDB return the #VALUE! error value.
• Use start_num to skip a specified number of characters. Using FIND as an example, suppose you are working with the text string "AYF0093.YoungMensApparel". To find the number of the first "Y" in the descriptive part of the text string, set start_num equal to 8 so that the serial-number portion of the text is not searched. FIND begins with character 8, finds find_text at the next character, and returns the number 9. FIND always returns the number of characters from the start of within_text, counting the characters you skip if start_num is greater than 1.

Example 1: FIND

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

Example 2: FIND nested within MID

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

Example 3: FINDB (with your computer set to a default language that supports DBCS)

In the following example:

• FINDB returns 3 because each character is counted by its bytes; the first character has 2 bytes, so the second character begins at byte 3.
• FIND returns 2 because " " is in the second position within the string . FIND returns 2 no matter what the default language setting is on your computer.

=FINDB

("

"," ")

equals 3

=FIND(" "," ")

equals 2

See Also

• EXACT function
• JIS function
• LEN, LENB functions
• MID, MIDB functions
• SEARCH, SEARCHB functions
• Text functions

Excel > Function reference > Text and data

FIXED function

Excel 2007

Rounds a number to the specified number of decimals, formats the number in decimal format using a period and commas, and returns the result as text.

Syntax

FIXED(number,decimals,no_commas)

Number   is the number you want to round and convert to text.

Decimals   is the number of digits to the right of the decimal point.

No_commas   is a logical value that, if TRUE, prevents FIXED from including commas in the returned text.

Remarks

• Numbers in Microsoft Excel can never have more than 15 significant digits, but decimals can be as large as 127.
• If decimals is negative, number is rounded to the left of the decimal point.
• If you omit decimals, it is assumed to be 2.
• If no_commas is FALSE or omitted, then the returned text includes commas as usual.
• The major difference between formatting a cell containing a number by using a command (On the Home tab, in the Number group, click the arrow next to Number, and then click Number.) and formatting a number directly with the FIXED function is that FIXED converts its result to text. A number formatted with the Cells command is still a number.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• DOLLAR function
• ROUND function
• TEXT function
• Text functions
• VALUE function

Excel > Function reference > Text and data

JIS function

The function described in this Help topic converts half-width (single-byte) letters within a character string to full-width (double-byte) characters. The name of the function (and the characters that it converts) depends upon your language settings.

For Japanese, this function changes half-width (single-byte) English letters or katakana within a character string to full-width (double-byte) characters.

Syntax

JIS(text)

Text   is the text or a reference to a cell that contains the text you want to change. If text does not contain any half-width English letters or katakana, text is not changed.

Example

=JIS("EXCEL")

equals "EXCEL"

=JIS("

")

equals " "

See Also

• Text functions

Excel > Function reference > Text and data

LEFT, LEFTB functions

Excel 2007

LEFT returns the first character or characters in a text string, based on the number of characters you specify.

LEFTB returns the first character or characters in a text string, based on the number of bytes you specify.

Important  LEFT is intended for use with languages that use the single-byte character set (SBCS), whereas LEFTB is intended for use with languages that use the double-byte character set (DBCS). The default language setting on your computer affects the return value in the following way:

• LEFT always counts each character, whether single-byte or double-byte, as 1, no matter what the default language setting is.
• LEFTB counts each double-byte character as 2 when you have enabled the editing of a language that supports DBCS and then set it as the default language. Otherwise, LEFTB counts each character as 1.

The languages that support DBCS include Japanese, Chinese (Simplified), Chinese (Traditional), and Korean.

For more information, see the following Help topics:

• Enable Windows XP support for certain languages (East Asian, Southeast Asian, Indic, and right-to-left languages)
• Enable entering text in another language in Microsoft Office

Syntax

LEFT(text,num_chars)

LEFTB(text,num_bytes)

Text   is the text string that contains the characters you want to extract.

Num_chars   specifies the number of characters you want LEFT to extract.

• Num_chars must be greater than or equal to zero.
• If num_chars is greater than the length of text, LEFT returns all of text.
• If num_chars is omitted, it is assumed to be 1.

Num_bytes   specifies the number of characters you want LEFTB to extract, based on bytes.

Example 1: LEFT

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

Example 2: LEFTB (with your computer set to a default language that supports DBCS)

In the following example:

• LEFTB returns the first 2 characters, because each character is counted as 2.
• LEFT returns the first 4 characters, because each character is counted as 1. LEFT returns the first 4 characters no matter what the default language setting is on your computer.

=LEFTB(" ",4) equals "

"

=LEFT(" ",4) equals "

"

See Also

• JIS function
• MID, MIDB functions
• Remove characters from text
• RIGHT, RIGHTB functions
• Text functions

Excel > Function reference > Text and data

LEN, LENB functions

Excel 2007

LEN returns the number of characters in a text string.

LENB returns the number of bytes used to represent the characters in a text string.

Important  LEN is intended for use with languages that use the single-byte character set (SBCS), whereas LENB is intended for use with languages that use the double-byte character set (DBCS). The default language setting on your computer affects the return value in the following way:

• LEN always counts each character, whether single-byte or double-byte, as 1, no matter what the default language setting is.
• LENB counts each double-byte character as 2 when you have enabled the editing of a language that supports DBCS and then set it as the default language. Otherwise, LENB counts each character as 1.

The languages that support DBCS include Japanese, Chinese (Simplified), Chinese (Traditional), and Korean.

For more information, see the following Help topics:

• Enable Windows XP support for certain languages (East Asian, Southeast Asian, Indic, and right-to-left languages)
• Enable entering text in another language in Microsoft Office

Syntax

LEN(text)

LENB(text)

Text   is the text whose length you want to find. Spaces count as characters.

Example 1: LEN

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

Example 2: LENB (with your computer set to a default language that supports DBCS)

In the following example:

• LENB returns 6, because each character is counted as 2.
• LEN returns 3, because each character is counted as 1. LEN returns 3 no matter what the default language setting is on your computer.

=LENB(" ")

equals 6

=LEN(" ")

equals 3

See Also

• Count unique values among duplicates
• EXACT function
• JIS function
• Remove characters from text
• Round a number
• SEARCH, SEARCHB functions
• Text functions

Excel > Function reference > Text and data

LOWER function

Excel 2007

Converts all uppercase letters in a text string to lowercase.

Syntax

LOWER(text)

Text   is the text you want to convert to lowercase. LOWER does not change characters in text that are not letters.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• Change the case of text
• PROPER function
• Text functions
• UPPER function

Excel > Function reference > Text and data

MID, MIDB functions

Excel 2007

MID returns a specific number of characters from a text string, starting at the position you specify, based on the number of characters you specify.

MIDB returns a specific number of characters from a text string, starting at the position you specify, based on the number of bytes you specify.

Important  MID is intended for use with languages that use the single-byte character set (SBCS), whereas MIDB is intended for use with languages that use the double-byte character set (DBCS). The default language setting on your computer affects the return value in the following way:

• MID always counts each character, whether single-byte or double-byte, as 1, no matter what the default language setting is.
• MIDB counts each double-byte character as 2 when you have enabled the editing of a language that supports DBCS and then set it as the default language. Otherwise, MIDB counts each character as 1.

The languages that support DBCS include Japanese, Chinese (Simplified), Chinese (Traditional), and Korean.

For more information, see the following Help topics:

• Enable Windows XP support for certain languages (East Asian, Southeast Asian, Indic, and right-to-left languages)
• Enable entering text in another language in Microsoft Office

Syntax

MID(text,start_num,num_chars)

MIDB(text,start_num,num_bytes)

Text   is the text string containing the characters you want to extract.

Start_num   is the position of the first character you want to extract in text. The first character in text has start_num 1, and so on.

Num_chars   specifies the number of characters you want MID to return from text.

Num_bytes   specifies the number of characters you want MIDB to return from text, in bytes.

Remarks

• If start_num is greater than the length of text, MID returns "" (empty text).
• If start_num is less than the length of text, but start_num plus num_chars exceeds the length of text, MID returns the characters up to the end of text.
• If start_num is less than 1, MID returns the #VALUE! error value.
• If num_chars is negative, MID returns the #VALUE! error value.
• If num_bytes is negative, MIDB returns the #VALUE! error value.

Example 1: MID

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

Example 2: MIDB (with your computer set to a default language that supports DBCS)

In the following example:

• MIDB returns " " because each character is counted as 2; the second argument specifies a starting point at the fourth byte, which is the second character, and the third argument specifies a length of 2 bytes, which is one character.
• MID returns " " because each character is counted as 1; the second argument specifies a starting point at the fourth character, and the third argument specifies a length of 2 characters. MID returns " " no matter what the default language setting is on your computer.

=MIDB(" ",4,2)

equals " "

=MID(" ",4,2)

equals " "

See Also

• CODE function
• FIND, FINDB functions
• JIS function
• LEFT, LEFTB functions
• RIGHT, RIGHTB functions
• SEARCH, SEARCHB functions
• Text functions

Excel > Function reference > Text and data

PHONETIC function

Extracts the phonetic (furigana) characters from a text string.

Syntax

PHONETIC(reference)

Reference   is a text string or a reference to a single cell or a range of cells that contain a furigana text string.

Remarks

• If reference is a range of cells, the furigana text string in the upper-left corner cell of the range is returned.
• If the reference is a range of nonadjacent cells, the #N/A error value is returned.

Example

If cells C4 contains " " and cell B7 contains " ", the following are true:

=PHONETIC(C4)

equals " "

=PHONETIC(B7)

equals " "

See Also

• Text functions

Excel > Function reference > Text and data

PROPER function

Excel 2007

Capitalizes the first letter in a text string and any other letters in text that follow any character other than a letter. Converts all other letters to lowercase letters.

Syntax

PROPER(text)

Text   is text enclosed in quotation marks, a formula that returns text, or a reference to a cell containing the text you want to partially capitalize.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• Change the case of text
• LOWER function
• Text functions
• UPPER function

Excel > Function reference > Text and data

REPLACE, REPLACEB functions

Excel 2007

REPLACE replaces part of a text string, based on the number of characters you specify, with a different text string.

REPLACEB replaces part of a text string, based on the number of bytes you specify, with a different text string.

Important  REPLACE is intended for use with languages that use the single-byte character set (SBCS), whereas REPLACEB is intended for use with languages that use the double-byte character set (DBCS). The default language setting on your computer affects the return value in the following way:

• REPLACE always counts each character, whether single-byte or double-byte, as 1, no matter what the default language setting is.
• REPLACEB counts each double-byte character as 2 when you have enabled the editing of a language that supports DBCS and then set it as the default language. Otherwise, REPLACEB counts each character as 1.

The languages that support DBCS include Japanese, Chinese (Simplified), Chinese (Traditional), and Korean.

For more information, see the following Help topics:

• Enable Windows XP support for certain languages (East Asian, Southeast Asian, Indic, and right-to-left languages)
• Enable entering text in another language in Microsoft Office

Syntax

REPLACE(old_text,start_num,num_chars,new_text)

REPLACEB(old_text,start_num,num_bytes,new_text)

Old_text   is text in which you want to replace some characters.

Start_num   is the position of the character in old_text that you want to replace with new_text.

Num_chars   is the number of characters in old_text that you want REPLACE to replace with new_text.

Num_bytes   is the number of bytes in old_text that you want REPLACEB to replace with new_text.

New_text   is the text that will replace characters in old_text.

Example 1: REPLACE

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

Example 2: REPLACEB (with your computer set to a default language that supports DBCS)

In the following example:

• REPLACEB returns " " because each character is counted as 2; the second argument specifies a starting point at the fourth byte, which is the second character, and the third argument specifies a length of 4 bytes, which is two characters.
• REPLACE returns " " because each character is counted as 1; the second argument specifies a starting point at the fourth character, and the third argument specifies a length of 4 characters. REPLACE returns " " no matter what the default language setting is on your computer.

=REPLACEB(" ",4,4," ")

equals " "

=REPLACE(" ",4,4," ")

equals " "

See Also

• JIS function
• MID, MIDB functions
• SEARCH, SEARCHB functions
• SUBSTITUTE function
• Text functions
• TRIM function

Excel > Function reference > Text and data

REPT function

Excel 2007

Repeats text a given number of times. Use REPT to fill a cell with a number of instances of a text string.

Syntax

REPT(text,number_times)

Text   is the text you want to repeat.

Number_times   is a positive number specifying the number of times to repeat text.

Remarks

• If number_times is 0 (zero), REPT returns "" (empty text).
• If number_times is not an integer, it is truncated.
• The result of the REPT function cannot be longer than 32,767 characters, or REPT returns #VALUE!.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• Display only the last four digits of identification numbers
• Repeat a character in a cell
• Text functions

Excel > Function reference > Text and data

RIGHT, RIGHTB functions

Excel 2007

RIGHT returns the last character or characters in a text string, based on the number of characters you specify.

RIGHTB returns the last character or characters in a text string, based on the number of bytes you specify.

Important  RIGHT is intended for use with languages that use the single-byte character set (SBCS), whereas RIGHTB is intended for use with languages that use the double-byte character set (DBCS). The default language setting on your computer affects the return value in the following way:

• RIGHT always counts each character, whether single-byte or double-byte, as 1, no matter what the default language setting is.
• RIGHTB counts each double-byte character as 2 when you have enabled the editing of a language that supports DBCS and then set it as the default language. Otherwise, RIGHTB counts each character as 1.

The languages that support DBCS include Japanese, Chinese (Simplified), Chinese (Traditional), and Korean.

For more information, see the following Help topics:

• Enable Windows XP support for certain languages (East Asian, Southeast Asian, Indic, and right-to-left languages)
• Enable entering text in another language in Microsoft Office

Syntax

RIGHT(text,num_chars)

RIGHTB(text,num_bytes)

Text   is the text string containing the characters you want to extract.

Num_chars   specifies the number of characters you want RIGHT to extract.

Num_bytes   specifies the number of characters you want RIGHTB to extract, based on bytes.

Remarks

• Num_chars must be greater than or equal to zero.
• If num_chars is greater than the length of text, RIGHT returns all of text.
• If num_chars is omitted, it is assumed to be 1.

Example 1: RIGHT

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

Example 2: RIGHTB (with your computer set to a default language that supports DBCS)

In the following example:

• RIGHTB returns the last 2 characters, because each character is counted as 2.
• RIGHT returns the last 4 characters, because each character is counted as 1. RIGHT returns the last 4 characters no matter what the default language setting is on your computer.

=RIGHTB(" ",4) equals "

"

=RIGHT(" ",4) equals "

"

See Also

• Display only the last four digits of identification numbers
• JIS function
• LEFT, LEFTB functions
• MID, MIDB functions
• Remove characters from text
• Text functions

Excel > Function reference > Text and data

SEARCH, SEARCHB functions

Excel 2007

This article describes the formula syntax and usage of the SEARCH and SEARCHB functions (function: A prewritten formula that takes a value or values, performs an operation, and returns a value or values. Use functions to simplify and shorten formulas on a worksheet, especially those that perform lengthy or complex calculations.) in Microsoft Office Excel.

Description

The SEARCH and SEARCHB functions locate one text string within a second text string, and return the number of the starting position of the first text string from the first character of the second text string. For example, to find the position of the letter "n" in the word "printer", you can use the following function:

=SEARCH("n","printer")

This function returns 4 because "n" is the fourth character in the word "printer."

You can also search for words within other words. For example, the function

=SEARCH("base","database")

returns 5, because the word "base" begins at the fifth character of the word "database". You can use the SEARCH and SEARCHB functions to determine the location of a character or text string within another text string, and then use the MID and MIDB functions to return the text, or use the REPLACE and REPLACEB functions to change the text. These functions are demonstrated in Example 1 in this article.

Important  The SEARCH function is intended for use with languages that use the single-byte character set (SBCS), whereas the SEARCHB function is intended for use with languages that use the double-byte character set (DBCS). The default language setting on your computer affects the return value in the following way:

• SEARCH always counts each character, whether single-byte or double-byte, as 1, no matter what the default language setting is.
• SEARCHB counts each double-byte character as 2 when you have enabled the editing of a language that supports DBCS and then set it as the default language. Otherwise, SEARCHB counts each character as 1.

The languages that support DBCS include Japanese, Chinese (Simplified), Chinese (Traditional), and Korean.

For more information, see the following Help topics:

• Enable Windows XP support for certain languages (East Asian, Southeast Asian, Indic, and right-to-left languages)
• Enable entering text in another language in Microsoft Office

Syntax

SEARCH(find_text, within_text, [start_num])

SEARCHB(find_text, within_text, [start_num])

The SEARCH and SEARCHB function syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):

• find_text  Required. The text that you want to find.
• within_text  Required. The text in which you want to search for the value of the find_text argument.
• start_num  Optional. The character number in the within_text argument at which you want to start searching.

Remarks

• The SEARCH and SEARCHB functions are not case sensitive. If you want to do a case sensitive search, you can use FIND and FINDB.
• You can use the wildcard characters — the question mark (?) and asterisk (*) — in the find_text argument. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.
• If the value of find_text is not found, the #VALUE! error value is returned.
• If the start_num argument is omitted, it is assumed to be 1.
• If start_num is not greater than 0 (zero) or is greater than the length of the within_text argument, the #VALUE! error value is returned.
• Use start_num to skip a specified number of characters. Using the SEARCH function as an example, suppose you are working with the text string "AYF0093.YoungMensApparel". To find the position of the first "Y" in the descriptive part of the text string, set start_num equal to 8 so that the serial number portion of the text (in this case, "AYF0093") is not searched. The SEARCH function starts the search operation at the eighth character position, finds the character that is specified in the find_text argument at the next position, and returns the number 9. The SEARCH function always returns the number of characters from the start of the within_text argument, counting the characters you skip if the start_num argument is greater than 1.

Example 1: SEARCH

The example may be easier to understand if you copy it to a blank worksheet.

How do I copy an example?

1. Select the example in this article.

Important  Do not select the row or column headers.

Selecting an example from Help

2. Press CTRL+C.
3. In Excel, create a blank workbook or worksheet.
4. In the worksheet, select cell A1, and press CTRL+V.

Important  For the example to work properly, you must paste it into cell A1 of the worksheet.

5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

Example 2: SEARCHB

Important  Your computer must be set to a default language that supports DBCS for this example to work.

In the following example:

• The SEARCHB function returns 3 because each character is counted by its bytes; the first character has 2 bytes, so the second character begins at byte 3.
• SEARCH returns 2 because " " is in the second position within the string. SEARCH returns 2 regardless of the default language setting on your computer.

=SEARCHB

("

"," ")

equals 3

=SEARCH(" "," ")

equals 2

See Also

• Check if a cell contains text (case-insensitive)
• FIND, FINDB functions
• JIS function
• MID, MIDB functions
• REPLACE, REPLACEB functions
• SUBSTITUTE function
• Text functions

Excel > Function reference > Text and data

SUBSTITUTE function

Excel 2007

Substitutes new_text for old_text in a text string. Use SUBSTITUTE when you want to replace specific text in a text string; use REPLACE when you want to replace any text that occurs in a specific location in a text string.

Syntax

SUBSTITUTE(text,old_text,new_text,instance_num)

Text   is the text or the reference to a cell containing text for which you want to substitute characters.

Old_text   is the text you want to replace.

New_text   is the text you want to replace old_text with.

Instance_num   specifies which occurrence of old_text you want to replace with new_text. If you specify instance_num, only that instance of old_text is replaced. Otherwise, every occurrence of old_text in text is changed to new_text.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• Remove spaces and nonprinting characters from text
• REPLACE, REPLACEB functions
• TEXT function
• Text functions

Excel > Function reference > Text and data

T function

Excel 2007

Returns the text referred to by value.

Syntax

T(value)

Value   is the value you want to test.

Remarks

• If value is or refers to text, T returns value. If value does not refer to text, T returns "" (empty text).
• You do not generally need to use the T function in a formula because Microsoft Excel automatically converts values as necessary. This function is provided for compatibility with other spreadsheet programs.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• CELL function
• N function
• Text functions
• VALUE function

Excel > Function reference > Text and data

TEXT function

Excel 2007

This article describes the formula syntax and usage of the TEXT function (function: A prewritten formula that takes a value or values, performs an operation, and returns a value or values. Use functions to simplify and shorten formulas on a worksheet, especially those that perform lengthy or complex calculations.) in Microsoft Office Excel.

Description

The TEXT function converts a numeric value to text and lets you specify the display formatting by using special format strings. This function is useful in situations where you want to display numbers in a more readable format, or you want to combine numbers with text or symbols. For example, suppose cell A1 contains the number 23.5. To format the number as a dollar amount, you can use the following formula:

=TEXT(A1,"$0.00")

In this example, Excel displays $23.50.

You can also format numbers by using the commands in the Number group on the Home tab of the Ribbon. However, these commands work only if the entire cell is numeric. If you want to format a number and combine it with other text, the TEXT function is the best option. For example, you can add text to the preceding formula:

=TEXT(A1,"$0.00") & " per hour"

Excel displays $23.50 per hour.

Syntax

TEXT(value, format_text)

The TEXT function syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):

• value  Required. A numeric value, a formula that evaluates to a numeric value, or a reference to a cell containing a numeric value.
• format_text  Required. A numeric format as a text string enclosed in quotation marks, for example "m/d/yyyy" or "#,##0.00". See the following sections for specific formatting guidelines.

Guidelines for number formats

• Display decimal places and significant digits  To format fractions or numbers that contain decimal points, include the following digit placeholders, decimal points, and thousand separators in the format_text argument.

• If a number has more digits to the right of the decimal point than there are placeholders in the format, the number rounds to as many decimal places as there are placeholders. If there are more digits to the left of the decimal point than there are placeholders, the extra digits are displayed. If the format contains only number signs (#) to the left of the decimal point, numbers less than 1 begin with a decimal point; for example, .47.

• Display a thousands separator  To display a comma as a thousands separator or to scale a number by a multiple of 1,000, include the following separator in the number format.

• Display hours, minutes, and seconds  To display time formats (such as hours, minutes, and seconds), use the following codes in the format_text argument.

•  Note    You must use the numeric keypad; using the ALT key with the number keys in the top row of the keyboard will not generate ANSI codes.
• Display percentages  To display numbers as a percentage of 100 — for example, to display .08 as 8% or 2.8 as 280% — include the percent sign (%) in the format_text argument.
• Display scientific notations  To display numbers in scientific (exponential) format, use the following exponent codes in the format_text argument.

See Also

• Calculate the difference between two times
• Combine text and numbers
• Combine text with a date or time
• Convert times
• DOLLAR function
• FIXED function
• Insert Julian dates
• Show dates as days of the week
• T function
• Text functions

Excel > Function reference > Text and data

TRIM function

Excel 2007

Removes all spaces from text except for single spaces between words. Use TRIM on text that you have received from another application that may have irregular spacing.

Important  The TRIM function was designed to trim the 7-bit ASCII space character (value 32) from text. In the Unicode character set, there is an additional space character called the nonbreaking space character that has a decimal value of 160. This character is commonly used in Web pages as the HTML entity,  . By itself, the TRIM function does not remove this nonbreaking space character. For an example of how to trim both space characters from text, see Remove spaces and nonprinting characters from text.

Syntax

TRIM(text)

Text   is the text from which you want spaces removed.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• CLEAN function
• MID, MIDB functions
• Remove spaces and nonprinting characters from text
• REPLACE, REPLACEB functions
• SUBSTITUTE function
• Text functions

Excel > Function reference > Text and data

UPPER function

Excel 2007

Converts text to uppercase.

Syntax

UPPER(text)

Text   is the text you want converted to uppercase. Text can be a reference or text string.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

Excel > Function reference > Text and data

VALUE function

Excel 2007

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

Syntax

VALUE(text)

Text   is the text enclosed in quotation marks or a reference to a cell containing the text you want to convert.

Remarks

• Text can be in any of the constant number, date, or time formats recognized by Microsoft Excel. If text is not in one of these formats, VALUE returns the #VALUE! error value.
• You do not generally need to use the VALUE function in a formula because Excel automatically converts text to numbers as necessary. This function is provided for compatibility with other spreadsheet programs.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

 Note    To view the number as a time, select the cell, and then on the Home tab, in the Number group, click the arrow next to Number Format, and click Time.