Microsoft Excel Functions & Formulas

Use the SUBSTITUTE function to substitute

parts of a cell

In this example, the “-” character needs to be replaced with a blank

space. But only the first occurrence of this character should be

replaced. To do this, type any kind of text and numbers in column A

as shown in the screenshot below, using the “-” character in differ-

ent positions and in a variety of occurrences.

4

To substitute parts of a cell:

1. Select cells B2:B10 and type the following formula:

=SUBSTITUTE(A2,"-","",1).

2. Press <Ctrl+Enter>.

Note: If you want to substitute the second occurrence

of this character, use the following formula:

=SUBSTITUTE(A2,"-","",2).

Text Functions

67

3

Figure 3-8 ...