Need to separate a word or text into one character per column in Excel? Whether you’re solving cryptograms, analyzing text, or preparing data for another worksheet, Excel provides a couple of easy ways to split text into individual characters.

In this tutorial, you’ll learn two methods for separating text into individual columns using Text to Columns and a formula that combines the MID and COLUMNS functions. You’ll also learn when each method works best and how the formula automatically updates as it’s copied across your worksheet.

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

What You’ll Learn

  • How to split text into one character per column
  • How to use Text to Columns with fixed-width data
  • How to separate text using the MID and COLUMNS functions
  • Why the formula updates automatically as it’s copied across columns
  • How to convert formulas into permanent values
  • Which method works best for your situation

Common Uses

  • Solving cryptograms
  • Creating cryptograms
  • Breaking words into individual letters
  • Text analysis
  • Classroom activities
  • Custom Excel projects
  • Preparing data for formulas or lookups
  • Learning Excel text functions

Requirements / Compatibility

This tutorial was originally recorded in 2020, and both methods continue 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 column.

Video Tutorial

Breaking Down the Concept

This tutorial demonstrates two different approaches to separating text into individual columns. One uses Excel’s built-in Text to Columns tool, while the other uses a formula that automatically extracts one character at a time.

Method 1: Text to Columns

Text to Columns is a built-in Excel feature that separates text based on a delimiter (such as a comma or space) or at fixed positions that you define.

In this tutorial, we use the Fixed Width option and manually place a break between every character.

Why it’s used:

  • Doesn’t require formulas.
  • Good for one-time text separation.
  • Easy for beginners to understand.

The downside is that adding a break between every character can become time-consuming for longer strings of text.

Method 2: MID + COLUMNS Formula

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

This formula automatically extracts one character at a time from your original text.

It combines two Excel functions that work together.

MID – Return One Character

=MID($A1,1,1)

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

It needs three pieces of information:

  • The text to search
  • The character position
  • The number of characters to return

In this tutorial, it always returns one character.

Why it’s used:

  • Pulls one letter from the original text.
  • Separates each character into its own cell.

COLUMNS – Count Across the Worksheet

=COLUMNS($A$1:A$1)

The COLUMNS function counts how many columns are included in a range.

As the formula is copied across the worksheet, the range expands:

=COLUMNS($A$1:A$1)   returns 1
=COLUMNS($A$1:B$1)   returns 2
=COLUMNS($A$1:C$1)   returns 3

That changing number becomes the character position used by the MID function.

So each copied formula automatically returns the next letter.

Why it’s used:

  • Automatically counts across columns.
  • Eliminates the need to manually change the formula.

Why Are There Dollar Signs ($)?

You may notice dollar signs in the formula: $A1 and $A$1:A$1

Dollar signs tell Excel which parts of a cell reference should stay fixed when a formula is copied.

In this example:

  • $A1 always points to column A, where the original text is stored.
  • The row number can still change if you copy the formula downward.
  • The second reference gradually expands as you copy across columns, allowing COLUMNS to count upward automatically.

Without these mixed and absolute references, Excel would begin looking in the wrong places as the formula was copied.

LEN – Estimate How Many Columns You Need (Optional)

=LEN(A1)

The LEN function counts the number of characters in a cell.

Although it isn’t required, it can be helpful for estimating how many columns you’ll need before copying the formula across.

Why it’s used:

  • Counts every character.
  • Helps size your worksheet before splitting the text.

Copy and Paste Values

Once the text has been separated, the worksheet still contains formulas.

If you want permanent letters instead:

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

This replaces the formulas with the displayed text.

Which Method Should You Use?

MethodBest For
Text to ColumnsOne-time tasks and users who prefer built-in tools
MID + COLUMNS FormulaReusable worksheets, automation, and larger projects

Step-by-Step Instructions

Method 1: Text to Columns

  1. Enter your text into column A.
  2. Select the cells containing the text.
  3. Go to Data → Text to Columns.
  4. Choose Fixed Width.
  5. Place a break between every character.
  6. Click Finish.

Method 2: MID + COLUMNS Formula

  1. Enter your text into column A.
  2. In the next cell, enter:
=MID($A1,COLUMNS($A$1:A$1),1)
  1. Press Enter.
  2. Drag the formula across until every character has been separated.
  3. (Optional) Copy the results and Paste Values.

Additional Tips

  • If you’ll be separating text regularly, the formula method is usually faster than manually creating fixed-width breaks.
  • The LEN function can help you estimate how many columns you’ll need.
  • Paste Values is helpful if you want to delete the original text or share the finished worksheet.
  • The formula works with letters, numbers, spaces, and punctuation.

Related Features

  • MID Function
  • LEN Function
  • TEXTJOIN
  • Flash Fill

Related

How to Remove Extra Spaces in Excel

Learn how to remove extra spaces from text in Microsoft Excel using the TRIM function. This beginner-friendly tutorial explains what TRIM removes, when to use it, and why it’s one of the most useful functions for cleaning imported or inconsistent data.

How to Hide Zero Values (0s) in Excel

Learn two easy ways to hide zero values in Microsoft Excel without changing your formulas. This step-by-step tutorial demonstrates both the built-in worksheet option and a Conditional Formatting method to create cleaner spreadsheets, reports, and templates. Includes updated instructions and screenshots for current versions of Excel.

How to Create an Order Form with Drop-Down Menus in Microsoft Excel

Learn how to create a professional order form in Microsoft Excel using drop-down menus, Data Validation, and named ranges. This step-by-step tutorial also covers formatting dates, ZIP codes, and credit card fields to build cleaner, more accurate spreadsheets for real-world business use.

Stay Updated

There are two easy ways to get notified when new tutorials are published:

Subscribe on YouTube for new video tutorials: @SimpleSoftwareTutorials

or enter your email below to receive new blog posts directly in your inbox: