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.
ROW: Returns the row number of a 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
- Copying the formula in below shown cells in column B.
The function will help to separate a string into substring in Microsoft Excel.