B4P Reference

Function Names

table replace cells, table replace cells seleted rows, table replace cells in columns, table replace cells selected rows in columns

Description

This function looks for cells using the right-hand comparison expression across all or selected columns and across all or selected rows. Once found, the cell will be replaced with new contents.

Call as: procedure or function

Restrictions

Indirect parameter passing is disabled
This function provides a table context for partial table specifications with table name and row number for selected function parameters

Parameter count

3-6

Parameters

No.

Type

Description

1.
input

string

target table name

The target table must exist.

Opt. 2.
code

expression
:string

Expression to select rows

Applicable to table replace cells selected rows and table replace cells selected rows in columns:
Specify the conditions or rules to select the rows. See expressions to select rows.
The table context for partial table specifications is available for referencing just the columns easily. Example: [Year]>=2022.
Attention: Comparison operators = and <> (instead of == and !=) may hijack the next function parameters for its own use as additional comparison operands, e.g. a[]=1,3,5. As long this comparison is not made in the last function parameter, then put parentheses around them, e.g. ... , (a[]=1,3,5), ....

Opt 2. / 3.
input

table columns

Header names and/or column numbers

See table columns as function parameters for general ruling for this parameter.
Specific rules apply for this function:

Any number of header names and column numbers may be specified

2 / 3 / 4
input

string
set

values to compare

Provide one string or multiple strings in a set to compare with the table contents. wildcards are supported if the strings are of type softquoted string.

3 / 4 / 5
input

string
set

new values

Provide one string or multiple strings in a set to replace the table contents if the corresponding comparisons apply. wildcards are supported if the strings are of type softquoted string. The nuber of set elements must match, unless one single value is provided here which will then be applied for all successful comparisons.

Opt. 4 / 5 / 6
input

string
set

options

One or multiple options may be applied.

above	See 1	Contents will be replaced in the cell above. Note: Combination of 'above' and 'below' will cancel each other. But OK to combine with 'left' or 'right'.
below	"	Contents will be replaced in the cell above. Note: See above
left	"	Contents will be replaced in the cell to the right. Note: Combination of 'left' and 'right' will cancel each other. But OK to combine with 'above' or 'below.
right	"	Contents will be replaced in teh cell to the right. See above
once	See 2	Do exactly one replacement
once each	"	Do exactly one replacement for every comparison value
append	See 3	The new value is appended to the existing value
strip	"	The new value is 'subtracted' from the existing value. Only the first matching string will be stripped.
ignore case		Search process ignores case, e.g. comparing 'ABC' with 'abc' yields true.
ignore blanks		Search process ignores blanks, e.g. comparing 'can not' and 'cannot' yields true.

Notes:
1. Combination of multiple terms are allowed as usage of every turn displaces the position. Example: Combining 'above' and 'left' replaces the contents in the field 1 row above and 1 column to the left. Combining 'left' and 'right', as well as 'above' and 'below' cancel each other out. Mulitple use of a term is also allowed, e.g. 'left', 'left' indicates 2 fields to the left. No contents are replaced if the final location lies beyond the first column, or on or above the header row.
2. 'once' and 'once each' cannot be combined.
3. 'append' and 'strip' cannot be combined.

Return value

Type	Description
numeral	Number of replacements made All fields which have been replaced successfully are counted. Matches with replacements to be done outside the table will not be counted.

Examples

table initialize( t,

               { { City,        State, Country },

                 { Tuscon,      AZ,    United States },

                 { Toronto,     ON,    Canada },

                 { Missisauga,  ON,    Canada },

                 { Zürich,      ZH,    Switzerland },

                 { Lake Zurich, MI,    United States } } );



table copy table ( t, u );

c[] = table replace cells( u, { United States, canada, 'swit*' }, { US, CA, CH }, ignore case );

table replace cells in columns( t, City, '*Zurich*', Fake Place, { 3:right } );

table list ( u );

echo("Number of replacements in first function call ", c[], new line );



table replace cells selected rows in columns( t, [Country]==Canada, State, ON, Ontario, once );

table replace cells in columns ( t, Country, { United States, Canada }, { US, CA }, once each );

table replace cells( t, US, ' (near California)', append );

table list ( t );

Output

    0 : City        | State | Country
    1 : Tuscon      | AZ    | US     
    2 : Toronto     | ON    | CA     
    3 : Missisauga  | ON    | CA     
    4 : Zürich      | ZH    | CH     
    5 : Lake Zurich | MI    | US     

Number of replacements in first function call 5

    0 : City        | State   | Country              |           
    1 : Tuscon      | AZ      | US (near California) |           
    2 : Toronto     | Ontario | CA                   |           
    3 : Missisauga  | ON      | Canada               |           
    4 : Zürich      | ZH      | Switzerland          |           
    5 : Lake Zurich | MI      | United States        | Fake Place

Try it yourself: Open LIB_Function_table_replace_cells.b4p in B4P_Examples.zip. Decompress before use.

table replace cells ...

Function Names

Description

Call as: procedure or function

Restrictions

Parameter count

Parameters

Return value

Examples

Output

See also