Remove characters from text

previous page next page

Use the LEN, LEFT, and RIGHT functions to do this task.

Worksheet example

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

Create a blank workbook or worksheet.
Select the example in the Help topic. Do not select the row or column headers.

Selecting an example from Help
Press CTRL+C.
In the worksheet, select cell A1, and press CTRL+V.
To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.

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)

Select the range of cells you want to search.
If you want to search the entire worksheet, click any cell in the worksheet.
On the Edit menu, click Replace.
In the Find what box, enter the text or numbers 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.
If you want to specify a format for your search, click Format and make your selections in the Find Format dialog box.
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.
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.
Click Find Next.
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.

previous page start next page