• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

Excelypedia

The Excel Encyclopedia of The Internet

  • Tutorials
  • Functions
  • Formulas
  • Templates

ARRAYTOTEXT in Excel

August 30, 2021 by Andrew Childress
arraytotext in Excel

You’ll find yourself with lists of data, you'll want to convert these text arrays into simple string sentence forms. Here's how to use ARRAYTOTEXT in Excel.

When you work with spreadsheets, you’ll often find yourself with lists of data. But sometimes, you’ll want to convert these text arrays into simple strings in sentence form. Fortunately, Excel offers a great way to do exactly that. Let’s learn how to use ARRAYTOTEXT in Microsoft Excel.

How to Use ARRAYTOTEXT in Excel

Imagine that you have a list of words inside a single column in Excel. Each cell contains a separate word: Texas, Nebraska, Oklahoma, and so forth. In many cases, the column layout is fine. But perhaps you want to use this list as part of a written narrative. It would pay to have the list array displayed as a simple text string.

This is where Excel’s ARRAYTOTEXT function comes in handy. ARRAYTOTEXT converts arrays into text strings, separated neatly by commas and single spaces. These display in a single cell, rather than a column array. Plus, they can be copied and pasted into other apps.

To use ARRAYTOTEXT, begin by clicking into any empty cell in your workbook. Then, type an = sign. This tells Excel that you’ll be inputting a formula.

Continue by typing ARRAYTOTEXT(. This inserts the ARRAYTOTEXT function into your formula.

arraytotext in Excel

Then, simply click and drag your cursor to select the array. This is the range of cells containing the data that you want to convert to a text string. In this case, the range is B1:B6. Once you do this, type ), and your formula will be complete:

=ARRAYTOTEXT(B1:B6)

Use arraytotext in Excel

Hit Enter on your keyboard, and Excel will convert the array into a text string with comma separators.

arraytotext formula in Excel

As you can see, ARRAYTOTEXT is a quick and easy Excel function that helps you reorganize data. With a few quick steps, you can transform a lengthy list into a streamlined and easy-to-read sentence. Try it any time you need to convert arrays into text.

Category: Excel FunctionsTag: Excel, How To, Instructions, Tutorial

About Andrew Childress

Previous Post: « Convert Excel functions CONVERT in Excel
Next Post: YEAR in Excel Year formula Excel »

Sidebar

Recent Posts

  • Format Cell Alignment in Excel
  • Default Width in Excel
  • Translate in Excel
  • Switch Windows in Excel
  • EXACT in Excel

Categories

  • Basics
  • Examples
  • Excel Formulas
  • Excel Functions
  • Excel Shortcuts
  • Excel Templates
  • Excel Tutorials
  • Uncategorized

Excelypedia

Master Excel to add a highly valuable asset to your employability portfolio. Learn how to analyze data in Excel.

  • Facebook
  • Twitter
  • Instagram
  • LinkedIn
  • YouTube

Learn

  • Functions
  • Formulas
  • Shortcuts

Legal

  • Privacy Policy
  • Cookie Policy
  • Terms & Conditions

Help

  • FAQ
  • Contact Us

Copyright © 2022 · Excelypedia · All Rights Reserved

Back to top