Text to Columns

Split text in cells into multiple columns

The Text to Columns feature allows users to split text content from cells into multiple columns based on delimiters or fixed widths. This is useful for importing data, cleaning up datasets, and reformatting text.

Overview

Text to Columns functionality enables users to:

Split comma-separated values (CSV) into columns
Parse tab-delimited or space-delimited text
Split by custom delimiters
Break up formatted text (e.g., "First Last" � "First" | "Last")
Clean and restructure imported data

Basic Usage

The onSplitTextToColumns callback is provided by useSpreadsheetState and is triggered when users request to split text in cells.

import {
  SpreadsheetProvider,
  CanvasGrid,
} from "@rowsncolumns/spreadsheet";
import { useSpreadsheetState } from "@rowsncolumns/spreadsheet-state";

function SpreadsheetWithTextSplit() {
  const {
    activeCell,
    activeSheetId,
    selections,
    onSplitTextToColumns,
    // ... other state
  } = useSpreadsheetState({
    // configuration
  });

  return (
    <SpreadsheetProvider>
      <CanvasGrid
        sheetId={activeSheetId}
        activeCell={activeCell}
        selections={selections}
        onSplitTextToColumns={onSplitTextToColumns}
        // ... other props
      />
    </SpreadsheetProvider>
  );
}

Function Signature

type OnSplitTextToColumns = (
  sheetId: number,
  activeCell: CellInterface,
  selections: SelectionArea<SelectionAttributes>[]
) => void;

How It Works

When onSplitTextToColumns is called:

The function analyzes the selected cells
Detects or prompts for a delimiter (comma, semicolon, tab, space, etc.)
Splits the text content based on the delimiter
Distributes the split values across adjacent columns
Preserves the original data structure

Example Usage

Manual Trigger

You can manually trigger text splitting from a button or menu:

function SpreadsheetToolbar() {
  const {
    activeCell,
    activeSheetId,
    selections,
    onSplitTextToColumns,
  } = useSpreadsheetState({
    // configuration
  });

  const handleSplitText = () => {
    onSplitTextToColumns?.(activeSheetId, activeCell, selections);
  };

  return (
    <button
      onClick={handleSplitText}
      className="px-3 py-1 bg-blue-500 text-white rounded"
    >
      Split Text to Columns
    </button>
  );
}

Add Text to Columns to the context menu:

import { DropdownMenu, DropdownMenuItem } from "@rowsncolumns/ui";

function CustomContextMenu({
  activeCell,
  activeSheetId,
  selections,
  onSplitTextToColumns,
}) {
  return (
    <DropdownMenu>
      <DropdownMenuItem
        onClick={() => onSplitTextToColumns?.(activeSheetId, activeCell, selections)}
      >
        Split Text to Columns
      </DropdownMenuItem>
      {/* Other menu items */}
    </DropdownMenu>
  );
}

Common Use Cases

Splitting Names

Split "First Last" into separate columns:

// Before:
// | John Doe | Jane Smith |

// After splitting by space:
// | John | Doe | Jane | Smith |

Parsing CSV Data

Split comma-separated values:

// Before:
// | apple,banana,cherry |

// After splitting by comma:
// | apple | banana | cherry |

Processing Addresses

Split full addresses into components:

// Before:
// | 123 Main St, New York, NY 10001 |

// After splitting by comma:
// | 123 Main St | New York | NY 10001 |

Complete Example

import React, { useState } from "react";
import {
  SpreadsheetProvider,
  CanvasGrid,
  Toolbar,
  ToolbarIconButton,
  Sheet,
} from "@rowsncolumns/spreadsheet";
import {
  useSpreadsheetState,
  SheetData,
  CellData,
} from "@rowsncolumns/spreadsheet-state";

function SpreadsheetWithSplitText() {
  const [sheets, setSheets] = useState<Sheet[]>([
    { sheetId: 1, rowCount: 100, columnCount: 26, title: "Data" }
  ]);
  const [sheetData, setSheetData] = useState<SheetData<CellData>>({});

  const {
    activeCell,
    activeSheetId,
    selections,
    getCellData,
    onChangeActiveCell,
    onChangeSelections,
    onChange,
    onSplitTextToColumns,
  } = useSpreadsheetState({
    sheets,
    sheetData,
    onChangeSheets: setSheets,
    onChangeSheetData: setSheetData,
  });

  const handleSplitText = () => {
    if (!onSplitTextToColumns) {
      console.warn("Split text to columns is not available");
      return;
    }

    onSplitTextToColumns(activeSheetId, activeCell, selections);
  };

  return (
    <SpreadsheetProvider>
      <div className="flex flex-col h-screen">
        <Toolbar>
          <ToolbarIconButton
            onClick={handleSplitText}
            title="Split Text to Columns"
          >
            <SplitIcon />
          </ToolbarIconButton>
        </Toolbar>

        <div className="flex-1">
          <CanvasGrid
            sheetId={activeSheetId}
            activeCell={activeCell}
            selections={selections}
            getCellData={getCellData}
            onChangeActiveCell={onChangeActiveCell}
            onChangeSelections={onChangeSelections}
            onChange={onChange}
            onSplitTextToColumns={onSplitTextToColumns}
          />
        </div>
      </div>
    </SpreadsheetProvider>
  );
}

export default SpreadsheetWithSplitText;

Custom Implementation

If you need custom text splitting logic, you can implement your own:

import { CellInterface, SelectionArea } from "@rowsncolumns/spreadsheet";

function customSplitTextToColumns(
  sheetId: number,
  activeCell: CellInterface,
  selections: SelectionArea<SelectionAttributes>[],
  delimiter: string = ","
) {
  // Get the selection range
  const selection = selections[0] || {
    range: {
      startRowIndex: activeCell.rowIndex,
      endRowIndex: activeCell.rowIndex,
      startColumnIndex: activeCell.columnIndex,
      endColumnIndex: activeCell.columnIndex,
    },
  };

  // Process each row in the selection
  for (
    let rowIndex = selection.range.startRowIndex;
    rowIndex <= selection.range.endRowIndex;
    rowIndex++
  ) {
    // Get the cell value
    const cellData = getCellData(
      sheetId,
      rowIndex,
      selection.range.startColumnIndex
    );

    if (!cellData?.formattedValue) continue;

    // Split the text
    const parts = cellData.formattedValue.split(delimiter);

    // Write parts to adjacent columns
    parts.forEach((part, index) => {
      const columnIndex = selection.range.startColumnIndex + index;

      // Update cell with split value
      onChange?.(
        sheetId,
        { rowIndex, columnIndex },
        part.trim(),
        undefined,
        false
      );
    });
  }
}

Delimiter Options

Common delimiters for text splitting:

const delimiters = {
  comma: ",",
  semicolon: ";",
  tab: "\t",
  space: " ",
  pipe: "|",
  colon: ":",
  custom: "...", // User-defined
};

Advanced Features

Multiple Delimiters

Split by multiple delimiters at once:

function splitByMultipleDelimiters(text: string, delimiters: string[]) {
  let result = [text];

  delimiters.forEach((delimiter) => {
    result = result.flatMap((part) => part.split(delimiter));
  });

  return result.map((part) => part.trim()).filter(Boolean);
}

// Example: Split by comma OR semicolon
const parts = splitByMultipleDelimiters("apple,banana;cherry", [",", ";"]);
// Result: ["apple", "banana", "cherry"]

Fixed Width Splitting

Split text at fixed character positions:

function splitFixedWidth(text: string, widths: number[]) {
  const parts: string[] = [];
  let position = 0;

  widths.forEach((width) => {
    parts.push(text.slice(position, position + width).trim());
    position += width;
  });

  // Add remaining text
  if (position < text.length) {
    parts.push(text.slice(position).trim());
  }

  return parts;
}

// Example: Split "JohnDoe  25Engineer"
const parts = splitFixedWidth("JohnDoe  25Engineer", [8, 4]);
// Result: ["JohnDoe", "25", "Engineer"]

Preserve Quoted Text

Don't split text within quotes:

function splitWithQuotes(text: string, delimiter: string) {
  const regex = new RegExp(
    `${delimiter}(?=(?:[^"]*"[^"]*")*[^"]*$)`,
    "g"
  );
  return text.split(regex).map((part) => part.replace(/^"|"$/g, "").trim());
}

// Example: Split 'apple,"banana,cherry",grape'
const parts = splitWithQuotes('apple,"banana,cherry",grape', ",");
// Result: ["apple", "banana,cherry", "grape"]

Keyboard Shortcuts

Add a keyboard shortcut for text splitting:

function SpreadsheetWithShortcuts() {
  const { onSplitTextToColumns } = useSpreadsheetState({
    // configuration
  });

  const handleKeyDown = (e: React.KeyboardEvent) => {
    // Ctrl/Cmd + Shift + T
    if ((e.ctrlKey || e.metaKey) && e.shiftKey && e.key === "T") {
      e.preventDefault();
      onSplitTextToColumns?.(activeSheetId, activeCell, selections);
    }
  };

  return (
    <CanvasGrid
      onKeyDown={handleKeyDown}
      onSplitTextToColumns={onSplitTextToColumns}
      // ... other props
    />
  );
}

Best Practices

Backup Data: Always preserve original data before splitting
Check Column Space: Ensure enough empty columns for split data
Handle Edge Cases: Account for empty cells and malformed data
Trim Whitespace: Remove leading/trailing spaces from split values
Validate Output: Verify the split operation produced expected results
Undo Support: Ensure split operations are undoable

Error Handling

function safeSplitTextToColumns(
  sheetId: number,
  activeCell: CellInterface,
  selections: SelectionArea<SelectionAttributes>[]
) {
  try {
    // Check if there's a selection
    if (!selections.length) {
      console.warn("No cells selected for splitting");
      return;
    }

    // Check if there's enough space for split data
    const estimatedColumns = 5; // Estimate or calculate
    const maxColumn = getSheetColumnCount(sheetId);
    const startColumn = activeCell.columnIndex;

    if (startColumn + estimatedColumns > maxColumn) {
      console.error("Not enough columns for split operation");
      return;
    }

    // Perform split
    onSplitTextToColumns?.(sheetId, activeCell, selections);
  } catch (error) {
    console.error("Error splitting text to columns:", error);
  }
}

Limitations

The split operation requires adjacent empty columns
Very long text may exceed column limits
Complex delimiters may require custom parsing
Large selections may impact performance

Use Cases

Data Import

Clean up imported CSV or TSV data:

// Import raw data as single column, then split

Name Parsing

Split full names into first and last names:

// "John Doe" � "John" | "Doe"

Email Processing

Extract username and domain from emails:

// "[email protected]" � "user" | "example.com"

Tag Parsing

Split tags or categories:

// "tag1,tag2,tag3" � "tag1" | "tag2" | "tag3"

Troubleshooting

Split Not Working

Verify onSplitTextToColumns is passed to CanvasGrid
Check that cells contain text data
Ensure there are empty columns for split data

Unexpected Results

Check delimiter selection
Verify text format (quotes, escapes, etc.)
Test with sample data first

Performance Issues

Limit selection size for large datasets
Consider batch processing for many cells
Use web workers for heavy split operations

PreviousUser-Defined Colors NextNavigate to Sheet Range

Last updated 1 day ago

Was this helpful?