Separating A String Into Excel Substring

While working on reports, you may want a formula that will derive results from a string into substrings.

You can use a combination of MID & ROW functions to derive the output.
MID: Returns the characters from the middle of a text string, given a starting position and length.

Syntax: =MID(text,start_num,num_chars)
ROW: Returns the row number of a reference.

Syntax: =ROW(reference)
Let us take an example:

• In cell A2, the value is “ABCDEFGHIJKLMNOPQR”

• We want a formula that will return the 3 characters from the string in the cell B2, then next 3 characters in cell B3 & so on.
• The formula in cell B2 would be
• =MID($A$2,(ROW()-ROW($A$2)+1)*3-2,3)

• Copying the formula in below shown cells in column B.

The function will help to separate a string into substring in Microsoft Excel.