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.
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)
Hit Enter on your keyboard, and Excel will convert the array into a text string with comma separators.
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.
CONVERT in Excel