Remove characters from text

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

Function details

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

    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.

1

2

3

A

Data

Vitamin A

Vitamin B1

Formula

Description (Result)

=LEFT(A2, LEN(A2)-2)

Removes last two characters from contents of A2 (Vitamin)

=RIGHT(A3, LEN(A3)-8)

Removes first 8 characters from A3 (B1)

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

Applies To: Excel 2007



Was this information helpful?

Yes No

How can we improve it?

255 characters remaining

To protect your privacy, please do not include contact information in your feedback. Review our privacy policy.

Thank you for your feedback!

Support resources

Change language