This approach is described below.
Create the array
The first step is to split the text string into an array of characters.
Where it fails, we get a #VALUE!
error:
The COUNT function then counts the numbers in the array and returns a final result of 2.
TheROW functionthen evaluates the text and returns a reference.
Note this is anarray formulaand must be entered with control + shift + enter in older versions of Excel.
Numbers include negative numbers, percentages, dates, times, fractions, and formulas that return numbers.
The array can be one dimensional, or two-dimensional, determined byrowsandcolumnsarguments.
For example, =MID(“apple”,2,3) returns “ppl”.
LEN will also count characters in numbers, but number formatting is not included.
INDIRECT Function
The Excel INDIRECT function returns a valid cell reference from a given text string.
INDIRECT is useful when you want to assemble a text value that can be used as a valid reference.
ROW Function
The Excel ROW function returns the row number for a reference.
For example, ROW(C5) returns 5, since C5 is the fifth row in the spreadsheet.
When no reference is provided, ROW returns the row number of the cell which contains the formula.
