# Text to 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.

```tsx
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

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

## How It Works

When `onSplitTextToColumns` is called:

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

## Example Usage

### Manual Trigger

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

```tsx
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>
  );
}
```

### Context Menu Integration

Add Text to Columns to the context menu:

```tsx
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:

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

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

### Parsing CSV Data

Split comma-separated values:

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

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

### Processing Addresses

Split full addresses into components:

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

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

## Complete Example

```tsx
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:

```tsx
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:

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

## Advanced Features

### Multiple Delimiters

Split by multiple delimiters at once:

```tsx
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:

```tsx
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:

```tsx
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:

```tsx
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

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

## Error Handling

```tsx
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:

```tsx
// Import raw data as single column, then split
```

### Name Parsing

Split full names into first and last names:

```tsx
// "John Doe" � "John" | "Doe"
```

### Email Processing

Extract username and domain from emails:

```tsx
// "user@example.com" � "user" | "example.com"
```

### Tag Parsing

Split tags or categories:

```tsx
// "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