Excel Formulas to Convert the String into Array ~ Quick Tricks!!

Are you looking for the formulas to convert the string into an array in Excel? If yes, then, this article is for you. Let’s get into this article!! Get an official version of ** MS Excel** from the following link: https://www.microsoft.com/en-in/microsoft-365/excel

General Formula:

Use the below formula to convert the string into an array, you can use the below formula.

{=MID(string,ROW(INDIRECT(“1:”&LEN(string))),1)}

Syntax Explanations:

MID – In Excel, the MID function extracts the number (starting from the left side) or characters from the given string
ROW – This function helps to return the row number for reference. Read more on the ROW function.
INDIRECT – The Excel INDIRECT function returns a valid reference from a given text string.
LEN – It is used to find the length of the text string. Read more on the LEN Function.
String – It represents the input ranges.
Comma symbol (,) – It is a separator that helps to separate a list of values.
Parenthesis () – The main purpose of this symbol is to group the elements.

Example:

Refer to the below example image.

First, we will enter the input values in Column B.
Here we need to convert the string into an array format.

Select any cell and type the above-given formula.

Finally, Press CTRL + SHIFT + ENTER keys to get the result out, if you need, drag the fill handle over range to apply the formula.

Wrap-Up:

From this chapter, you can get to know the formulas to convert the string into an array in Excel. Let me know if you have any doubts regarding this article or any other Excel/VBA topic.

Thank you so much for visiting *Geek Excel!! **If you want to learn more helpful formulas, check out Excel Formulas *!! **