# Transform Cells with LAMBDA Formula

## Content

The Transform Cells feature allows users to apply custom single-parameter [LAMBDA](https://developer.mescius.com/spreadjs/docs/formulareference/FormulaFunctions/other-functions/lambda-function "https://developer.mescius.com/spreadjs/docs/formulareference/FormulaFunctions/other-functions/lambda-function") formulas to selected cell ranges. Users can choose how the formula parameter is scoped—by cell, row, column, or entire range.
After conversion, the source cell range is cleared, and the converted range is applied to the worksheet. Cell styles remain unchanged after conversion; only stored cell values change.

> type=note
> **Notes:**
>
> * Valid Formula: `=LAMBDA(x, <expression using x>)`
> * Only **one continuous range** is supported.
> * Only **normal worksheet ranges** are supported—not on report sheets, table sheets, Gantt sheets, tables, pivot tables, or data ranges.
> * When a transformation yields a **dynamic array** result, the output will spill across adjacent cells based on the **maximum number of rows and columns** generated across all transformations. Refer to Sample 3.
> * If the transformation output exceeds the bounds of the current worksheet range, the excess portion will be clipped.

## Usage Instructions

1. Select a single continuous cell range on a normal worksheet.
2. Right-click and choose **Transform Cells**.
<br>
    ![image](https://cdn.mescius.io/document-site-files/images/7719ad0a-f083-46d7-aff6-f63e2e187c15/image.194949.png?width=250)
3. In the dialog:
    a. Enter a single-parameter LAMBDA formula in the **Formula** field. Supports auto-suggesting formulas during input.
    ![cellTransform](https://cdn.mescius.io/document-site-files/images/7719ad0a-f083-46d7-aff6-f63e2e187c15/cellTransform.bdd9f2.gif?width=400)
    b. Select the desired **Scope**.
    ![image](https://cdn.mescius.io/document-site-files/images/7719ad0a-f083-46d7-aff6-f63e2e187c15/image.b9b9d8.png?width=400)
4. Click **OK**. The original range is cleared and replaced with transformed values. Styles remain unchanged.

## Samples

**Sample 1: Sum the Industrial Data by Column**

* Source Cell Range:
<br>
    ![image](https://cdn.mescius.io/document-site-files/images/7719ad0a-f083-46d7-aff6-f63e2e187c15/image.b6d366.png?width=400)
* Formula: `=LAMBDA(x, SUM(x))`
* Scope: By Column

![cellTransform_sum](https://cdn.mescius.io/document-site-files/images/7719ad0a-f083-46d7-aff6-f63e2e187c15/cellTransform_sum.f17774.gif?width=750)
**Sample 2: Convert Text to Uppercase by Cell**

* Source Cell Range:
    ![image](https://cdn.mescius.io/document-site-files/images/7719ad0a-f083-46d7-aff6-f63e2e187c15/image.275075.png?width=400)
* Formula: `=LAMBDA(x, UPPER(x))`
* Scope: By Cell

![cellTransform_upper](https://cdn.mescius.io/document-site-files/images/7719ad0a-f083-46d7-aff6-f63e2e187c15/cellTransform_upper.0f6848.gif?width=750)
**Sample 3: Split Order Details by Semicolon and Comma**

* Source Cell Range:
    ![image](https://cdn.mescius.io/document-site-files/images/7719ad0a-f083-46d7-aff6-f63e2e187c15/image.c86812.png?width=400)
* Formula: `=LAMBDA(x, TEXTSPLIT(x, ";", ","))`
* Scope: By Cell

![cellTransform_textsplict](https://cdn.mescius.io/document-site-files/images/7719ad0a-f083-46d7-aff6-f63e2e187c15/cellTransform_textsplict.4efa83.gif?width=750)
The original data has a 3x2 structure. When transformed,

* each row expands to a maximum of 3 sub-rows based on the defined splitting logic, so the total number of rows becomes 3 (original rows) × 3 (max expanded sub-rows per row) = 9.
* for columns, the content is split and reorganized across 4 columns after transformation.

In summary, the process ensures each row scales to its full potential, resulting in a final output of 9 rows by 4 columns that systematically presents all segmented information.