Change the case of text

Let's say that you want to convert text from uppercase to lowercase or from lowercase to proper case to make it more readable. To change the case of text, you can use the UPPER, LOWER, or PROPER functions as shown in the following example.

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.

 Note    You can use these functions to change the case of text in one cell at a time only. To change the case of text for a range of cells, you can use these functions in an array formula (array formula: A formula that performs multiple calculations on one or more sets of values, and then returns either a single result or multiple results. Array formulas are enclosed between braces { } and are entered by pressing CTRL+SHIFT+ENTER.). For more information, see Guidelines and examples of array formulas.

For more information about how to use these functions, see UPPER function, LOWER function, and PROPER function.

See Also

• Change the font or font size in Excel

Excel > Formula and name basics > Examples of formulas > Text

Check if a cell contains text (case-insensitive)

Excel 2007

Let's say you want to ensure that a column contains text and not numbers or you want to find all orders by a particular salesperson. There are several ways to check if a cell contains text but the case of the text doesn't matter to you.

You can also use a filter to find text. For more information, see Filter data.

What do you want to do?

Find cells that contain text

Check if a cell has any text in it

Check if a cell matches specific text

Check if part of a cell matches specific text

Find cells that contain text

1. Select the range (range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.) of cells that you want to search.

If you want to search the entire worksheet, click any cell.

2. On the Home tab, in the Editing group, click Find & Select, and then click Find.
3. In the Find what box, enter the text or numbers that you want to search for or choose a recent search from the Find what drop-down box.

 Note    You can use wildcard characters in your search criteria.

4. If you want to specify a format for your search, click Format and make your selections in the Find Format dialog box.
5. Click Options to further define your search. For example, you can search for all of the cells that contain the same kind of data, such as formulas.

In the Within box, you can select Sheet or Workbook to search a worksheet or an entire workbook.

6. Click Find All or Find Next.

Find All lists every occurrence of the item that you are searching for and allows you to make a cell active by selecting a specific occurrence. You can sort the results of a Find All search by clicking a header.

 Note    To cancel a search in progress, press ESC.

 Top of Page

Check if a cell has any text in it

To do this task, use the ISTEXT function.

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.

Function details

ISTEXT

 Top of Page

Check if a cell matches specific text

To do this task, use the IF function. The IF function returns results that you specify based on a condition.

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.

Function details

IF

 Top of Page

Check if part of a cell matches specific text

To do this task, use the IF, SEARCH and ISNUMBER functions.

 Note    The SEARCH function is case-insensitive.

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.

Function details

IF

ISNUMBER

SEARCH, SEARCHB

 Top of Page

Excel > Formula and name basics > Examples of formulas > Text

Check if a cell contains text (case-sensitive)

Excel 2007

Let's say you want to find text that begins with a standard company prefix, such as ID_ or EMP-, and this text must be in uppercase letters. There are several ways to check if a cell contains text and the case of the text matters to you.

What do you want to do?

Compare one cell to another cell

Compare one value to a list of values

Check if part of a cell matches specific text

Compare one cell to another cell

To do this task, use the EXACT function.

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    EXACT is case-sensitive but ignores formatting differences.

Function details

EXACT

 Top of Page

Compare one value to a list of values

To do this task, use the EXACT and OR functions.

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    The formula in the example must be entered as an array formula (array formula: A formula that performs multiple calculations on one or more sets of values, and then returns either a single result or multiple results. Array formulas are enclosed between braces { } and are entered by pressing CTRL+SHIFT+ENTER.). After copying the example to a blank worksheet, select each formula cell individually. Press F2, and then press CTRL+SHIFT+ENTER. If the formula is not entered as an array formula, the error #VALUE! is returned.

Function details

OR

EXACT

 Top of Page

Check if part of a cell matches specific text

To do this task, use the IF, FIND, and ISNUMBER functions.

 Note    The FIND function is case-sensitive.

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.

The formula above uses the following arguments.

Formula to check for text

search_for: What you want to check for.

to_search: The cell that contains the text that you want to check.

Function details

IF

ISNUMBER

FIND, FINDB

 Top of Page

Excel > Formula and name basics > Examples of formulas > Text

Combine text and numbers

Excel 2007

Let's say you want to create a grammatically correct sentence from several columns of data for a mass mailing or format numbers with text without affecting formulas that use those numbers. There are several ways to combine text and numbers.

What do you want to do?

Display text before or after a number in a cell by using a number format

Combine text and numbers from different cells into the same cell by using a formula

Display text before or after a number in a cell by using a number format

If a column that you want to sort contains both numbers and text (such as Product #15, Product #100, Product #200), it may not sort as expected. You can use a number format to add text without changing the sorting behavior of the number.

1. Select the cells that you want to format.
2. On the Home tab, in the Number group, click the arrow, and then click More.
3. In the Category list, click a category, and then click a built-in format that resembles the one that you want.
4. In the Category list, click Custom.
5. In the Type box, edit the number format codes to create the format that you want.

To display both text and numbers in a cell, enclose the text characters in double quotation marks (" ") or precede the numbers with a backslash (\). Editing a built-in format does not remove the format.

 Top of Page

Combine text and numbers from different cells into the same cell by using a formula

To do this task, use the CONCATENATION and TEXT functions and the ampersand (&) operator.

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 the use of the TEXT function in the formula. When you join a number to a string of text by using the concatenation operator, use the TEXT function to format the number. The formula uses the underlying value from the referenced cell (.4 in this example) — not the formatted value you see in the cell (40%). The TEXT function restores the number formatting.

Functions details

CONCATENATE

TEXT

 Top of Page

Excel > Formula and name basics > Examples of formulas > Text

Combine text with a date or time

Excel 2007

Let's say you want to create a grammatically correct sentence from several columns of data for a mass mailing or format dates with text without affecting formulas that use those dates. To combine text with a date or time, use the TEXT function and the & (ampersand) operator.

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    You can copy the number format code used in a cell into a formula. Select the cell, and then on the Home tab, in the Number group, click the arrow, click More, click Custom in the Category list, select the custom format that you want in the Type box, and then copy the contents.

Functions details

TEXT

Excel > Formula and name basics > Examples of formulas > Text

Combine first and last names

Excel 2007

Let's say you want to create a single Full Name column by combining two other columns, First Name and Last Name. To combine first and last names, use the CONCATENATE function or the ampersand (&) operator.

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 replace the formula with the results, select the cells, and on the Home tab, in the Clipbboard group, click Copy , click Paste , and then click Paste Values.

Function details

CONCATENATE

Excel > Formula and name basics > Examples of formulas > Text

Combine two or more columns by using a function

Excel 2007

Let's say you have two or more columns of data that you want to combine in a single column, such as the name and address of a person. To combine two or more columns, use the CONCATENATE function. You can also use the CHAR function and an ASCII code to insert a character, such as a line break, when you're combining the data.

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 see the result without breaking the lines of the name or address, expand the width of column A to at least 17.00 units or 124 pixels.

Function details

CONCATENATE

CHAR

ASCII character chart

Excel > Formula and name basics > Examples of formulas > Text

Display only the last four digits of identification numbers

Excel 2007

Let's say, for common security measures, you want to display only the last four digits of an identification or Social Security number, credit card number, or other number and replace the rest of the digits with asterisks. Whether the digits in your cell are formatted as text, numbers, or the special Social Security number format, you can use the same functions. To display only the last four digits of identification numbers, use the CONCATENATE, RIGHT, and REPT functions.

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.

Security  In the example, you should hide column B and protect the worksheet so that the original data cannot be viewed.

Function details

CONCATENATE

RIGHT, RIGHTB

REPT

Excel > Formula and name basics > Examples of formulas > Text

Insert the current Excel file name, path, or worksheet in a cell

Excel 2007

Let's say you want to add information to a spreadsheet report that confirms the location of a workbook and worksheet so you can quickly track and identify it. There are several ways you can do this task.

What do you want to do?

Insert the current file name, its full path, and the name of the active worksheet

Insert the current file name and the name of the active worksheet

Insert the current file name only

Insert the current file name, its full path, and the name of the active worksheet

Type or paste the following formula in the cell in which you want to display the current file name with its full path and the name of the current worksheet:

=CELL("filename")

 Top of Page

Insert the current file name and the name of the active worksheet

Type or paste the following formula as an array formula (array formula: A formula that performs multiple calculations on one or more sets of values, and then returns either a single result or multiple results. Array formulas are enclosed between braces { } and are entered by pressing CTRL+SHIFT+ENTER.) to display the current file name and active worksheet name:

=RIGHT(CELL("filename"),LEN(CELL("filename"))- MAX(IF(NOT(ISERR(SEARCH("\",CELL("filename"), ROW(1:255)))),SEARCH("\",CELL("filename"),ROW(1:255)))))

 Notes 

• To enter a formula as an array formula, press CTRL+SHIFT+ENTER.
• The formula returns the name of the worksheet as long as the worksheet has been saved at least once. If you use this formula on an unsaved worksheet, the formula cell will remain blank until you save the worksheet.

 Top of Page

Insert the current file name only

Type or paste the following formula to insert the name of the current file in a cell:

=MID(CELL("filename"),SEARCH("[",CELL("filename"))+1, SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1)

 Note    If you use this formula on an unsaved worksheet, you receive the error #VALUE!. When you save the worksheet, the error is replaced by the file name.

 Top of Page

Excel > Formula and name basics > Examples of formulas > Text

Repeat a character in a cell

Excel 2007

Let's say you want to create a fixed column of special characters to format a report or add periods to the end of a text column to provide a Tab leader similar to a table of contents. To repeat a character in a cell, use the REPT function.

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.

Function details

REPT

Excel > Formula and name basics > Examples of formulas > Text

Remove spaces and nonprinting characters from text

Excel 2007

Sometimes text values contain leading, trailing, or multiple embedded space characters (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.) values 32 and 160), or non-printing characters (Unicode character set values 0 to 31, 127, 129, 141, 143, 144, and 157). These characters can sometimes cause unexpected results when you sort, filter, or search. For example, users may make typographical errors by inadvertently adding extra space characters, or imported text data from external sources may contain nonprinting characters embedded in the text. Because these characters are not easily noticed, the unexpected results may be difficult to understand. To remove these unwanted characters, you can use a combination of the TRIM, CLEAN, and SUBSTITUTE functions.

The TRIM function removes spaces from text except for single spaces between words. The CLEAN function removes all nonprintable characters from text. Both functions were designed to work with 7-bit ASCII, which is a subset of the ANSI character set (ANSI character set: An 8-bit character set used by Microsoft Windows that allows you to represent up to 256 characters (0 through 255) by using your keyboard. The ASCII character set is a subset of the ANSI set.). It's important to understand that the first 128 values (0 to 127) in 7-bit ASCII represent the same characters as the first 128 values in the Unicode character set.

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.

The CLEAN function was designed to remove the first 32 non-printing characters in the 7 bit ASCII code (values 0 through 31) from text. In the Unicode 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.

To do this task, use the SUBSTITUTE function to replace the higher value Unicode characters with the 7-bit ASCII characters for which the TRIM and CLEAN functions were designed.

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.

Function details

TRIM

CLEAN

SUBSTITUTE

Excel > Formula and name basics > Examples of formulas > Text

Remove characters from text

Excel 2007

Let's say you want to split a text column of phone numbers formatted as 999-999-9999 into three columns of numbers: area code, prefix, and number. There are several ways to remove characters from text.

What do you want to do?

Remove a specified number of characters from the right or left side of text

Remove a text string from a worksheet by using the Replace command

Remove a specified number of characters from the right or left side of text

To do this task, use the LEN, LEFT, and RIGHT functions.

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.

Function details

LEN, LENB

LEFT, LEFTB

RIGHT, RIGHTB

 Top of Page

Remove a text string from a worksheet by using the Replace command

1. Select the range (range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.) of cells that you want to search.

If you want to search the entire worksheet, click any cell in the worksheet.

2. On the Home tab, in the Editing group, click Find & Replace, and then click Replace.
3. In the Find what box, enter the text or numbers that you want to search for or choose a recent search from the Find what drop-down box.

 Note    You can use wildcard characters in your search criteria.

4. If you want to specify a format for your search, click Format and make your selections in the Find Format dialog box.
5. Click Options to further define your search. For example, you can search for all of the cells that contain the same kind of data, such as formulas.

In the Within box, you can select Home or Workbook to search a worksheet or an entire workbook.

6. In the Replace with box, enter the replacement characters and specific formats if necessary.

If you want to delete the characters in the Find what box, leave the Replace with box blank.

7. Click Find Next.
8. To replace the highlighted occurrence or all occurrences of the found characters, click Replace or Replace All.

 Note    To cancel a search in progress, press ESC.

 Top of Page

Excel > Formula and name basics > Examples of formulas > Text

Split names by using the Convert Text to Columns Wizard

Excel 2007

Use the Convert Text to Columns Wizard to separate simple cell content, such as first names and last names, into different columns.

Depending on your data, you can split the cell content based on a delimiter, such as a space or a comma, or based on a specific column break location within your data.

What do you want to do?

Split content based on a delimiter

Split space-delimited content

Split comma-delimited content

Split cell content based on a column break

Split content based on a delimiter

Use this method if your names have a delimited format, such as "First_name Last_name" (where the space between First_name and Last_name is the delimiter) or "Last_name, First_name" (where the comma is the delimiter).

Split space-delimited content

To complete these steps, copy the following sample data 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.

3. Press CTRL+C.
4. On the worksheet, select cell A1, and then 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.

1. Select the range of data that you want to convert.
2. On the Data tab, in the Data Tools group, click Text to Columns.
3. In Step 1 of the Convert Text to Columns Wizard, click Delimited, and then click Next.
4. In Step 2, select the Space check box, and then clear the other check boxes under Delimiters.

The Data preview box shows the first and last names in two separate columns.

5. Click Next.
6. In Step 3, click a column in the Data preview box, and then click Text under Column data format.

Repeat this step for each column in the Data preview box.

7. If you want to insert the separated content into the columns next to the full name, click the icon to the right of the Destination box, and then select the cell next to the first name in the list (B2, in this example).

Important  If you do not specify a new destination for the new columns, the split data will replace the original data.

8. Click the icon to the right of the Convert Text to Columns Wizard.

9. Click Finish.

 Top of Page

Split comma-delimited content

To complete these steps, copy the following sample data 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.

3. Press CTRL+C.
4. On the worksheet, select cell A1, and then 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 Formula tab, in the Formula Auditing group, click the Show Formulas button.

1. Select the range of data that you want to convert.
2. On the Data tab, in the Data Tools group, click Text to Columns.
3. In Step 1 of the Convert Text to Columns Wizard, click Delimited, and then click Next.
4. In Step 2, select the Comma check box, and then clear the other check boxes under Delimiters.

The Data preview box displays the first names and last names in two separate lists.

5. Click Next.
6. In Step 3, click a column in the Data preview box, and then click Text under Column data format.

Repeat this step for each column in the Data preview box.

7. If you want to show the separated content in the columns next to the full name, click the icon to the right of the Destination box, and then select the cell next to the first name in the list (B2, in this example).

Important  If you do not specify a new destination for the new columns, the divided data will replace the combined data.

8. Click the icon to the right of the Convert Text to Columns Wizard.

9. Click Finish.

 Top of Page

Split cell content based on a column break

You can also customize how you want your data to be separated by specifying a fixed column break location.

1. Select the cell or range of cells.
2. On the Data tab, in the Data Tools group, click Text to Columns.
3. In Step 1 of the Convert Text to Columns Wizard, click Fixed Width, and then click Next.
4. In the Data preview box, drag a line to indicate where you want the content to be divided.

Tip  To delete a line, double-click it.

5. Click Next.
6. In Step 3, select a column in the Data preview box, and then click a format option under Column data format.

Repeat this step for each column in the Data preview box.

7. If you want to show the split content in the columns next to the full name, click the icon to the right of the Destination box, and then click the cell next to the first name in the list.

Important  If you do not specify a new destination for the new columns, the divided data will replace the original data.

8. Click the icon to the right of the Convert Text to Columns Wizard.

9. Click Finish.

 Top of Page

See Also

• Split text among columns by using functions

Excel > Formula and name basics > Examples of formulas > Text

Split text among columns by using functions

Excel 2007

Text functions are useful for manipulating strings in your data, for example, distributing the first, middle, and last names from a cell into three separate columns.

This article demonstrates how to use combinations of the following text functions to extract and copy name components into separate cells.

Extracting name components

The key to distributing name components when you use text functions is the position of each character within a text string. The positions of the spaces within the text string are important because they indicate the beginning or end of name components in a string.

For example, in a cell that contains only a first and last name, the last name begins after the first instance of a space. Some names in your list may contain a middle name, in which case, the last name begins after the second instance of a space.

This article shows you how to extract various components from a variety of name formats. Click an example name in the following table to see the formulas for extracting the components from that name.

 Note    The formulas for each example may be easier to understand if you copy them to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example text and formulas in the table.

 Note    Do not select the row or column headers.

3. Press CTRL+C.
4. In the worksheet, select cell A1, and then 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 1: Jeff Smith

In this example, there are only two components: first name and last name. A single space separates the two name components.

 Note    In the following graphics, the highlight in the full name shows the character that the matching SEARCH formula is looking for.

First name

The first name starts with the first character in the string (J) and ends at the fifth character (the space). The formula returns five characters in A2, starting from the left.

Use the SEARCH function to find the value for num_chars:

Search for the numeric position of the space in A2, starting from the left. (5)

Last name

The last name starts at the space, five characters from the right, and ends at the last character on the right (h). The formula extracts five characters in A2, starting from the right.

Use the SEARCH and LEN functions to find the value for num_chars:

Search for the numeric position of the space in A2, starting from the left. (5)

Count the total length of the text string, and then subtract the number of characters from the left to the first space, as found in step 1. (10 - 5 = 5)

 Top of Page

Example 2: Eric S. Kurjan

In this example, there are three components in the full name: first name, middle initial, and last name. A space separates each name component.

 Note    In the following graphics, the highlight in the full name shows the character that the matching SEARCH formula is looking for.

First name

The first name starts with the first character from the left (E) and ends at the fifth character (the first space). The formula extracts the first five characters in A2, starting from the left.

Use the SEARCH function to find the value for num_chars:

Search for the numeric position of the first space in A2, starting from the left. (5)

Middle name

The middle name starts at the sixth character position (S), and ends at the eighth position (the second space). This formula involves nesting SEARCH functions to find the second instance of a space.

The formula extracts three characters, starting from the sixth position.

Use the SEARCH function to find the value for start_num:

Search for the numeric position of the first space in A2, starting from the first character from the left. (5)

Add 1 to get the position of the character after the first space (S). This numeric position is the starting position of the middle name. (5 + 1 = 6)

Use nested SEARCH functions to find the value for num_chars:

Search for the numeric position of the first space in A2, starting from the first character from the left. (5)

Add 1 to get the position of the character after the first space (S). The result is the character number at which you want to start searching for the second instance of space. (5 + 1 = 6)

Search for the second instance of space in A2, starting from the sixth position (S) found in step 4. This character number is the ending position of the middle name. (8)

Search for the numeric position of space in A2, starting from the first character from the left. (5)

Take the character number of the second space found in step 5 and subtract the character number of the first space found in step 6. The result is the number of characters MID extracts from the text string starting at the sixth position found in step 2. (8 – 5 = 3)

Last name

The last name starts six characters from the right (K) and ends at the first character from the right (n). This formula involves nesting SEARCH functions to find the second and third instances of a space (which are at the fifth and eighth positions from the left).

The formula extracts six characters in A2, starting from the right.

Use the LEN and nested SEARCH functions to find the value for num_chars:

Search for the numeric position of space in A2, starting from the first character from the left. (5)

Add 1 to get the position of the character after the first space (S). The result is the character number at which you want to start searching for the second instance of space. (5 + 1 = 6)

Search for the second instance of space in A2, starting from the sixth position (S) found in step 2. This character number is the ending position of the middle name. (8)

Count the total length of the text string in A2, and then subtract the number of characters from the left up to the second instance of space found in step 3. The result is the number of characters to be extracted from the right of the full name. (14 – 8 = 6)

 Top of Page

Example 3: Janaina B. G. Bueno

In this example, there are two middle initials. The first and third instances of space separate the name components.

 Note    In the following graphics, the highlight in the full name shows the character that the matching SEARCH formula is looking for.

First name

The first name starts with the first character from the left (J) and ends at the eighth character (the first space). The formula extracts the first eight characters in A2, starting from the left.

Use the SEARCH function to find the value for num_chars:

Search for the numeric position of the first space in A2, starting from the left. (8)

Middle name

The middle name starts at the ninth position (B), and ends at the fourteenth position (the third space). This formula involves nesting SEARCH to find the first, second, and third instances of space in the eighth, eleventh, and fourteenth positions.

The formula extracts five characters, starting from the ninth position.

Use the SEARCH function to find the value for start_num:

Search for the numeric position of the first space in A2, starting from the first character from the left. (8)

Add 1 to get the position of the character after the first space (B). This numeric position is the starting position of the middle name. (8 + 1 = 9)

Use nested SEARCH functions to find the value for num_chars:

Search for the numeric position of the first space in A2, starting from the first character from the left. (8)

Add 1 to get the position of the character after the first space (B). The result is the character number at which you want to start searching for the second instance of space. (8 + 1 = 9)

Search for the second space in A2, starting from the ninth position (B) found in step 4. (11)

Add 1 to get the position of the character after the second space (G). This character number is the starting position at which you want to start searching for the third space. (11 + 1 = 12)

Search for the third space in A2, starting at the twelfth position found in step 6. (14)

Search for the numeric position of the first space in A2. (8)

Take the character number of the third space found in step 7 and subtract the character number of the first space found in step 6. The result is the number of characters MID extracts from the text string starting at the ninth position found in step 2.

Last name

The last name starts five characters from the right (B) and ends at the first character from the right (o). This formula involves nesting SEARCH to find the first, second, and third instances of space.

The formula extracts five characters in A2, starting from the right of the full name.

Use nested SEARCH and the LEN functions to find the value for the num_chars:

Search for the numeric position of the first space in A2, starting from the first character from the left. (8)

Add 1 to get the position of the character after the first space (B). The result is the character number at which you want to start searching for the second instance of space. (8 + 1 = 9)

Search for the second space in A2, starting from the ninth position (B) found in step 2. (11)

Add 1 to get the position of the character after the second space (G). This character number is the starting position at which you want to start searching for the third instance of space. (11 + 1 = 12)

Search for the third space in A2, starting at the twelfth position (G) found in step 6. (14)

Count the total length of the text string in A2, and then subtract the number of characters from the left up to the third space found in step 5. The result is the number of characters to be extracted from the right of the full name. (19 - 14 = 5)

 Top of Page

Example 4: Kahn, Wendy Beth

In this example, the last name comes before the first name, and the middle name appears at the end. The comma marks the end of the last name, and a space separates each name component.

 Note    In the following graphics, the highlight in the full name shows the character that the matching SEARCH formula is looking for.

First name

The first name starts with the seventh character from the left (W) and ends at the twelfth character (the second space). Because the first name occurs at the middle of the full name, you need to use the MID function to extract the first name.

The formula extracts six characters, starting from the seventh position.

Use the SEARCH function to find the value for start_num:

Search for the numeric position of the first space in A2, starting from the first character from the left. (6)

Add 1 to get the position of the character after the first space (W). This numeric position is the starting position of the first name. (6 + 1 = 7)

Use nested SEARCH functions to find the value for num_chars:

Search for the numeric position of the first space in A2, starting from the first character from the left. (6)

Add 1 to get the position of the character after the first space (W). The result is the character number at which you want to start searching for the second space. (6 + 1 = 7)

Search for the second space in A2, starting from the seventh position (W) found in step 4. (12)

Search for the numeric position of the first space in A2, starting from the first character from the left. (6)

Take the character number of the second space found in step 5 and subtract the character number of the first space found in step 6. The result is the number of characters MID extracts from the text string starting at the seventh position found in step 2. (12 - 6 = 6)

Middle name

The middle name starts four characters from the right (B), and ends at the first character from the right (h). This formula involves nesting SEARCH to find the first and second instances of space in the sixth and twelfth positions from the left.

The formula extracts four characters, starting from the right.

Use nested SEARCH and the LEN functions to find the value for start_num:

Search for the numeric position of the first space in A2, starting from the first character from the left. (6)

Add 1 to get the position of the character after the first space (W). The result is the character number at which you want to start searching for the second space. (6 + 1 = 7)

Search for the second instance of space in A2 starting from the seventh position (W) found in step 2. (12)

Count the total length of the text string in A2, and then subtract the number of characters from the left up to the second space found in step 3. The result is the number of characters to be extracted from the right of the full name. (16 - 12 = 4)

Last name

The last name starts with the first character from the left (K) and ends at the fourth character (n). The formula extracts four characters, starting from the left.

Use the SEARCH function to find the value for num_chars:

Search for the numeric position of the first space in A2, starting from the first character from the left. (6)

Subtract 2 to get the numeric position of the ending character of the last name (n). The result is the number of characters you want LEFT to extract. (6 - 2 =4)

 Top of Page

Example 5: Mary Kay D. Andersen

In this example, there are two parts of the first name: Mary Kay. The second and third spaces separate each name component.

 Note    In the following graphics, the highlight in the full name shows the character that the matching SEARCH formula is looking for.

First name

The first name starts with the first character from the left and ends at the ninth character (the second space). This formula involves nesting SEARCH to find the second instance of space from the left.

The formula extracts nine characters, starting from the left.

Use nested SEARCH functions to find the value for num_chars:

Search for the numeric position of the first space in A2, starting from the first character from the left. (5)

Add 1 to get the position of the character after the first space (K). The result is the character number at which you want to start searching for the second instance of space. (5 + 1 = 6)

Search for the second instance of space in A2, starting from the sixth position (K) found in step 2. The result is the number of characters LEFT extracts from the text string. (9)

Middle name

The middle name starts at the tenth position (D), and ends at the twelfth position (the third space). This formula involves nesting SEARCH to find the first, second, and third instances of space.

The formula extracts two characters from the middle, starting from the tenth position.

Use nested SEARCH functions to find the value for start_num:

Search for the numeric position of the first space in A2, starting from the first character from the left. (5)

Add 1 to get the character after the first space (K). The result is the character number at which you want to start searching for the second space. (5 + 1 = 6)

Search for the position of the second instance of space in A2, starting from the sixth position (K) found in step 2. The result is the number of characters LEFT extracts from the left. (9)

Add 1 to get the character after the second space (D). The result is the starting position of the middle name. (9 + 1 = 10)

Use nested SEARCH functions to find the value for num_chars:

Search for the numeric position of the character after the second space (D). The result is the character number at which you want to start searching for the third space. (10)

Search for the numeric position of the third space in A2, starting from the left. The result is the ending position of the middle name. (12)

Search for the numeric position of the character after the second space (D). The result is the beginning position of the middle name. (10)

Take the character number of the third space, found in step 6, and subtract the character number of “D”, found in step 7. The result is the number of characters MID extracts from the text string starting at the tenth position found in step 4. (12 - 10 = 2)

Last name

The last name starts eight characters from the right. This formula involves nesting SEARCH to find the first, second, and third instances of space in the fifth, ninth, and twelfth positions.

The formula extracts eight characters from the right.

Use nested SEARCH and the LEN functions to find the value for num_chars:

Search for the numeric position of the first space in A2, starting from the left. (5)

Add 1 to get the character after the first space (K). The result is the character number at which you want to start searching for the space. (5 + 1 = 6)

Search for the second space in A2, starting from the sixth position (K) found in step 2. (9)

Add 1 to get the position of the character after the second space (D). The result is the starting position of the middle name. (9 + 1 = 10)

Search for the numeric position of the third space in A2, starting from the left. The result is the ending position of the middle name. (12)

Count the total length of the text string in A2, and then subtract the number of characters from the left up to the third space found in step 5. The result is the number of characters to be extracted from the right of the full name. (20 - 12 = 8)

 Top of Page

Example 6: Paula Barreto de Mattos

In this example, there are three parts of the last name: Barreto de Mattos. The first space marks the end of the first name and the beginning of the last name.

 Note    In the following graphics, the highlight in the full name shows the character that the matching SEARCH formula is looking for.

First name

The first name starts with the first character from the left (P) and ends at the sixth character (the first space). The formula extracts six characters from the left.

Use the Search function to find the value for num_chars:

Search for the numeric position of the first space in A2, starting from the left. (6)

Last name

The last name starts seventeen characters from the right (B) and ends with first character from the right (s). The formula extracts seventeen characters from the right.

Use the LEN and SEARCH functions to find the value for num_chars:

Search for the numeric position of the first space in A2, starting from the left. (6)

Count the total length of the text string in A2, and then subtract the number of characters from the left up to the first space, found in step 1. The result is the number of characters to be extracted from the right of the full name. (23 - 6 = 17)

 Top of Page

Example 7: James van Eaton

In this example, there are two parts of the last name: van Eaton. The first space marks the end of the first name and the beginning of the last name.

 Note    In the following graphics, the highlight in the full name shows the character that the matching SEARCH formula is looking for.

First name

The first name starts with the first character from the left (J) and ends at the eighth character (the first space). The formula extracts six characters from the left.

Use the SEARCH function to find the value for num_chars:

Search for the numeric position of the first space in A2, starting from the left. (6)

Last name

The last name starts with the ninth character from the right (v) and ends at the first character from the right (n). The formula extracts nine characters from the right of the full name.

Use the LEN and SEARCH functions to find the value for num_chars:

Search for the numeric position of the first space in A2, starting from the left. (6)

Count the total length of the text string in A2, and then subtract the number of characters from the left up to the first space, found in step 1. The result is the number of characters to be extracted from the right of the full name. (15 - 6 = 9)

 Top of Page

Example 8: Bacon Jr., Dan K.

In this example, the last name comes first, followed by the suffix. The comma separates the last name and suffix from the first name and middle initial.

 Note    In the following graphics, the highlight in the full name shows the character that the matching SEARCH formula is looking for.

First name

The first name starts with the twelfth character (D) and ends with the fifteenth character (the third space). The formula extracts three characters, starting from the twelfth position.

Use nested SEARCH functions to find the value for start_num:

Search for the numeric position of the first space in A2, starting from the left. (6)

Add 1 to get the character after the first space (J). The result is the character number at which you want to start searching for the second space. (6 + 1 = 7)

Search for the second space in A2, starting from the seventh position (J), found in step 2. (11)

Add 1 to get the character after the second space (D). The result is the starting position of the first name. (11 + 1 = 12)

Use nested SEARCH functions to find the value for num_chars:

Search for the numeric position of the character after the second space (D). The result is the character number at which you want to start searching for the third space. (12)

Search for the numeric position of the third space in A2, starting from the left. The result is the ending position of the first name. (15)

Search for the numeric position of the character after the second space (D). The result is the beginning position of the first name. (12)

Take the character number of the third space, found in step 6, and subtract the character number of “D”, found in step 7. The result is the number of characters MID extracts from the text string starting at the twelfth position, found in step 4. (15 - 12 = 3)

Middle name

The middle name starts with the second character from the right (K). The formula extracts two characters from the right.

Use nested SEARCH functions to find the value for num_chars:

Search for the numeric position of the first space in A2, starting from the left. (6)

Add 1 to get the character after the first space (J). The result is the character number at which you want to start searching for the second space. (6 + 1 = 7)

Search for the second space in A2, starting from the seventh position (J), found in step 2. (11)

Add 1 to get the character after the second space (D). The result is the starting position of the first name. (11 + 1 = 12)

Search for the numeric position of the third space in A2, starting from the left. The result is the ending position of the middle name. (15)

Count the total length of the text string in A2, and then subtract the number of characters from the left up to the third space, found in step 5. The result is the number of characters to be extracted from the right of the full name. (17 - 15 = 2)

Last name

The last name starts at the first character from the left (B) and ends at sixth character (the first space). Therefore, the formula extracts six characters from the left.

Use the SEARCH function to find the value for num_chars:

Search for the numeric position of the first space in A2, starting from the left. (6)

Suffix

The suffix starts at the seventh character from the left (J), and ends at ninth character from the left (.). The formula extracts three characters, starting from the seventh character.

Use the SEARCH function to find the value for start_num:

Search for the numeric position of the first space in A2, starting from the left. (6)

Add 1 to get the character after the first space (J). The result is the starting position of the suffix. (6 + 1 = 7)

Use nested SEARCH functions to find the value for num_chars:

Search for the numeric position of the first space in A2, starting from the left. (6)

Add 1 to get the numeric position of the character after the first space (J). The result is the character number at which you want to start searching for the second space. (7)

Search for the numeric position of the second space in A2, starting from the seventh character found in step 4. (11)

Subtract 1 from the character number of the second space found in step 4 to get the character number of “,”. The result is the ending position of the suffix. (11 - 1 = 10)

and Search for the numeric position of the character after the first space (J), also found in steps 3 and 4. (7)

Take the character number of “,” found in step 6, and subtract the character number of “J”, found in steps 3 and 4. The result is the number of characters MID extracts from the text string starting at the seventh position, found in step 2. (10 - 7 = 3)

 Top of Page

Example 9: Gary Altman III

In this example, the first name is at the beginning of the string and the suffix is at the end of the name. The formula to use for the name components is similar to Example 2, where the first name can be extracted by using the LEFT function, the last name can be extracted by using the MID function, and the suffix can be extracted by using the RIGHT function.

 Note    In the following graphics, the highlight in the full name shows the character that the matching SEARCH formula is looking for.

First name

The first name starts at the first character from the left (G) and ends at the fifth character (the first space). Therefore, the formula extracts five characters from the left of the full name.

Search for the numeric position of the first space in A2, starting from the left. (5)

Last name

The last name starts at the sixth character from the left (A) and ends at the eleventh character (the second space). This formula involves nesting SEARCH to find the positions of the spaces.

The formula extracts six characters from the middle, starting from the sixth character.

Use the SEARCH function to find the value for start_num:

Search for the numeric position of the first space in A2, starting from the left. (5)

Add 1 to get the position of the character after the first space (A). The result is the starting position of the last name. (5 + 1 = 6)

Use nested SEARCH functions to find the value for num_chars:

Search for the numeric position of the first space in A2, starting from the left. (5)

Add 1 to get the position of the character after the first space (A). The result is the character number at which you want to start searching for the second space. (5 + 1 = 6)

Search for the numeric position of the second space in A2, starting from the sixth character found in step 4. This character number is the ending position of the last name. (12)

and Search for the numeric position of the character after the first space (A), also found in steps 3 and 4. (6)

Take the character number of the second space, found in step 5, and then subtract the character number of “A”, found in steps 6 and 7. The result is the number of characters MID extracts from the text string, starting at the sixth position, found in step 2. (12 - 6 = 6)

Suffix

The suffix starts three characters from the right. This formula involves nesting SEARCH to find the positions of the spaces.

Use nested SEARCH and the LEN functions to find the value for num_chars:

Search for the numeric position of the first space in A2, starting from the left. (5)

Add 1 to get the character after the first space (A). The result is the character number at which you want to start searching for the second space. (5 + 1 = 6)

Search for the second space in A2, starting from the sixth position (A), found in step 2. (12)

Count the total length of the text string in A2, and then subtract the number of characters from the left up to the second space, found in step 3. The result is the number of characters to be extracted from the right of the full name. (15 - 12 = 3)

 Top of Page

Example 10: Mr. Ryan Ihrig

In this example, the full name is preceded by a prefix. The formulas to use for the name components are similar to Example 2, where the first name can be extracted by using the MID function and the last name can be extracted by using the RIGHT function.

 Note    In the following graphics, the highlight in the full name shows the character that the matching SEARCH formula is looking for.

First name

The first name starts at the fifth character from the left (R) and ends at the ninth character (the second space). This formula involves nesting SEARCH to find the positions of the spaces.

The formula extracts four characters, starting from the fifth position.

Use the SEARCH function to find the value for the start_num:

Search for the numeric position of the first space in A2, starting from the left. (4)

Add 1 to get the position of the character after the first space (R). The result is the starting position of the first name. (4 + 1 = 5)

Use nested SEARCH function to find the value for num_chars:

Search for the numeric position of the first space in A2, starting from the left. (4)

Add 1 to get the position of the character after the first space (R). The result is the character number at which you want to start searching for the second space. (4 + 1 = 5)

Search for the numeric position of the second space in A2, starting from the fifth character, found in steps 3 and 4. This character number is the ending position of the first name. (9)

and Search for the numeric position of the character after the first space (R), also found in steps 3 and 4. (5)

Take the character number of the second space, found in step 5, and then subtract the character number of “R”, found in steps 6 and 7. The result is the number of characters MID extracts from the text string, starting at the fifth position found in step 2. (9 - 5 = 4)

Last name

The last name starts five characters from the right. This formula involves nesting SEARCH to find the positions of the spaces.

Use nested SEARCH and the LEN functions to find the value for num_chars:

Search for the numeric position of the first space in A2, starting from the left. (4)

Add 1 to get the position of the character after the first space (R). The result is the character number at which you want to start searching for the second space. (4 + 1 = 5)

Search for the second space in A2, starting from the fifth position (R), found in step 2. (9)

Count the total length of the text string in A2, and then subtract the number of characters from the left up to the second space, found in step 3. The result is the number of characters to be extracted from the right of the full name. (14 - 9 = 5)

 Top of Page

Example 11: Julie Taft-Rider

In this example, the last name is hyphenated. A space separates each name component.

 Note    In the following graphics, the highlight in the full name shows the character that the matching SEARCH formula is looking for.

First name

The first name starts at the first character from the left and ends at the sixth position (the first space). The formula extracts six characters from the left.

Use the SEARCH function to find the value of num_chars:

Search for the numeric position of the first space in A2, starting from the left. (6)

Last name

The entire last name starts ten characters from the right (T) and ends at the first character from the right (r).

Use the LEN and SEARCH functions to find the value for num_chars:

Search for the numeric position of the space in A2, starting from the first character from the left. (6)

Count the total length of the text string to be extracted, and then subtract the number of characters from the left up to the first space, found in step 1. (16 - 6 = 10)