Function Problems

How to Split Every Character in an Excel Cell into Separate Cells

Maira Mehtab Sep 27, 2026 869 views

Question details

The user wants to separate a string of text within a single cell so that every individual character, whether a letter, number, or symbol, is placed into its own adjacent cell.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Preparing data for detailed character-by-character analysis or reformatting text strings across columns.
Observed behavior: The goal state is to dynamically or manually distribute each character from a source cell into contiguous columns in the same row.

Before you start

Ensure you have enough empty adjacent columns to the right of your target cells before applying these formulas, as the extracted characters will overwrite any existing data in their path.

Solution 1Recommended

Use the Dynamic Array Formula (Modern Excel)

This is the most efficient method for newer versions of Excel, utilizing dynamic arrays to automatically spill characters across adjacent columns without manual dragging.

Modern versions of Excel (such as Microsoft 365 and Excel 2021) support dynamic arrays. By combining the MID, SEQUENCE, and LEN functions, you can extract every character seamlessly.

Select the target cell

Click on the empty cell immediately to the right of the cell containing your text (for example, B1 if your text is in A1).

Enter the dynamic formula

Type the formula =MID(A1,SEQUENCE(1,LEN(A1)),1) into the formula bar.

Apply the formula

Press the Enter key. The characters will automatically spill into the adjacent columns to the right.

Spill Feature: Because this relies on Excel's spill behavior, you only need to enter the formula in one cell per row.

Solution 2

Use the Traditional MID and COLUMN Formula (Older Versions)

Ideal for older versions of Excel that do not support the SEQUENCE function or dynamic arrays.

Solution 3

Use a VBA Macro for Automated Splitting

Best for automating repetitive tasks across large datasets, provided macros are enabled in your workbook.

Advanced Spreadsheet Capabilities

Easily Split Text and Data with WPS Office

WPS Spreadsheet offers powerful text manipulation functions, including MID, LEN, and COLUMN. It is fully compatible with Excel formulas, allowing you to split text character-by-character effortlessly.

1. Open your spreadsheet: Launch WPS Office and open your dataset in WPS Spreadsheet.
2. Select the target cell: Click on the cell adjacent to the text you want to split.
3. Input the text extraction formula: Type the formula =MID($A1,COLUMN()-2,1) (adjusting the column offset as needed).
4. Apply across columns: Press Enter, then drag the cell's fill handle to the right and down to split all your text characters.

Fully compatible with Microsoft Excel formulas, functions, and .xlsx formats.Powerful data manipulation tools for splitting text and processing characters quickly.Lightweight and user-friendly interface for faster spreadsheet processing.Free to use across Windows, Mac, and mobile platforms.

microsoft office alternative - wps office

Frequently Asked Questions

How do I split text by spaces instead of every single character?

You can use the Text to Columns feature. Navigate to the Data tab, click 'Text to Columns', choose 'Delimited', and check the box for 'Space'. This will separate words into different cells instead of individual letters.

Why does the SEQUENCE formula return a #NAME? error?

The SEQUENCE function is a dynamic array function available only in newer versions of Excel or Microsoft 365. If your version does not support it, it will return a #NAME? error. Use the traditional MID and COLUMN formula instead.

How can I enable macros to use the VBA solution?

To enable macros, go to File > Options > Trust Center > Trust Center Settings > Macro Settings, and select 'Enable VBA macros'. Be sure to save your file as a Macro-Enabled Workbook (.xlsm) so your code is not lost.

Excel & Data Problems