Need to separate a word or text into one letter per row in Excel? Whether you’re creating puzzles, analyzing text, or preparing data for another worksheet, Excel can automatically split a piece of text into individual characters using a simple formula.

In this tutorial, you’ll learn how to separate a text string into individual rows using the MID and ROWS functions. You’ll also learn why these functions work together and how to convert the results into permanent values when you’re finished.

If you want to…Use
Put each character below the previous oneSplit into Rows (continue with this tutorial)
Put each character beside the previous oneSplit into Columns (use this tutorial instead)

What You’ll Learn

  • How to split text into one character per row
  • How the MID function extracts individual characters
  • How the ROWS function changes automatically as you copy the formula
  • Why the formula updates correctly when copied downward
  • How to convert formulas into permanent text values

Common Uses

  • Creating word puzzles or cryptograms
  • Breaking text into individual letters
  • Preparing data for analysis
  • Building custom Excel projects
  • Educational activities
  • Data manipulation
  • Learning Excel text functions

Requirements / Compatibility

This tutorial was originally recorded in 2020, and the formula continues to work in current versions of Microsoft Excel, including:

  • Microsoft 365
  • Excel 2024
  • Excel 2021
  • Excel 2019
  • Excel 2016
  • Excel for Mac
  • Excel for the web

No special add-ins or advanced features are required.

Practice & Resources

No practice files are required for this tutorial.

Simply enter a word or text string into a worksheet and follow the steps below to separate each character into its own row.

Video Tutorial

Breaking Down the Formula

The formula used in this tutorial combines two Excel functions that work together to extract one character at a time from a cell.

=MID(A$1,ROWS($A$1:$A1),1)

Let’s break it down.

MID – Extract One Character

=MID(A$1,1,1)

The MID function returns characters from the middle of a text string.

It has three parts:

  • Text – the cell containing your text
  • Starting position – where Excel should begin
  • Number of characters – how many characters to return

In this tutorial, we always return one character, but the starting position changes as the formula is copied downward.

Why it’s used:

  • Pulls one letter from the original text.
  • Allows each character to be placed into its own cell.

ROWS – Count as the Formula Moves Down

=ROWS($A$1:$A1)

The ROWS function counts how many rows are included in a range.

When the formula is copied downward, the range gradually grows:

=ROWS($A$1:$A1)   returns 1
=ROWS($A$1:$A2)   returns 2
=ROWS($A$1:$A3)   returns 3

That changing number becomes the starting position for the MID function.

So the first formula returns the first letter…

The second formula returns the second letter…

The third formula returns the third letter…

…and so on until the entire word has been separated.

Why it’s used:

  • Automatically counts upward without typing 1, 2, 3 manually.
  • Makes the formula easy to copy down a column.

Why Are There Dollar Signs ($)?

You may notice the formula contains dollar signs: A$1

The dollar sign locks the row reference, ensuring Excel always looks back to the original cell containing your text.

Without it, Excel would begin looking at different cells as you copied the formula downward, causing incorrect results.

This type of reference is called a mixed cell reference because part of the reference stays fixed while the other part changes.

Why Copy and Paste Values?

After you’ve separated the text, the cells still contain formulas.

If you want permanent letters instead of formulas:

  1. Copy the results.
  2. Choose Paste Special → Values.

This replaces the formulas with their current results.

Step-by-Step Instructions

  1. Enter your text into cell A1.
  2. In A2, enter:
=MID(A$1,ROWS($A$1:$A1),1)
  1. Press Enter.
  2. Drag the fill handle down until every character has been separated into its own row.
  3. (Optional) Copy the results and Paste Values if you no longer need the formulas.

Additional Tips

  • If you drag the formula farther than the length of your text, Excel will simply return blank cells.
  • Paste Values is helpful if you plan to delete the original text later.
  • This same concept can be adapted to split text into columns instead of rows.
  • The formula works with letters, numbers, spaces, and punctuation.

Related Features

  • MID Function
  • LEFT Function
  • RIGHT Function
  • LEN Function
  • TEXTJOIN
  • Flash Fill

Enjoyed this tutorial? Subscribe to my YouTube channel for more step-by-step software tutorials, tips, and practical ways to work more efficiently.