SUBSTITUTE

The numerical or string values to convert. This can be a constant value, a column, a cell range, or an expression evaluating to any of the above. For the format definition of data, see the "Macro Function Parameters" section in the chapter in this guide for your IBM® product.

from_table

A column containing values you want to convert. The length of the from_table column must be the same as to_table.

to_table

A column containing the values to which to convert. The length of the to_table column must be the same as from_table.

Description

SUBSTITUTE converts values in data using the substitution pairs specified in from_table and to_table. Each value in data found in from_table is substituted with the value in the corresponding row of to_table.

SUBSTITUTE can be used to change both numerical and string values. It always returns a data range with the same dimensions as data.

If you use SUBSTITUTE to convert strings to numbers or vice versa, all values in data must be converted. That is, every value in data must appear in from_table. Otherwise, the resulting row contains ???.

Examples

TEMP = SUBSTITUTE(COLUMN(1,5,10),COLUMN(1), COLUMN(7))

Creates a new column named TEMP, which contains the values 7, 5, 10 (the value 1 is replaced with 7).

TEMP = SUBSTITUTE(COLUMN("blue", "red"), COLUMN("blue", "red"), COLUMN(0, 1))

Creates a new column named TEMP, which contains the values 0 and 1 (the string "blue" is replaced with 0 and "red" is replaced with 1).

TEMP = SUBSTITUTE(V1, V2, V3)

Creates a new column named TEMP containing the values of column V1, where any values found in column V2 are replaced with the value in the corresponding row of column V3.

TEMP = SUBSTITUTE(V1:V2, V4, V5)

Creates two new columns named TEMP and VX, each with values from columns V1 and V2 respectively, where any values found in column V4 are replaced with the value in the corresponding row of column V5.

TEMP = SUBSTITUTE(V1[10:20]:V2, V4, V5)

Creates two new columns named TEMP and VX, each with values from rows 10-20 of columns V1 and V2 respectively, where any values found in column V4 are replaced with the value in the corresponding row of column V5.

Related Functions

Function	Description
EXTRACT	Extracts rows given the values in a predicate column
ISMEMBER	Tests an input range against a "table" of values, returning one if a value is contained in the table, else zero