CODEWITHSUNDEEP
excelexcel-formulaexcel-2010
Sybeus
Sybeus

Reputation: 1189

Excel formula to generate array of all prefixes for a string

Given any string of numbers, I would like to generate an array of all string prefixes, using a formula in Excel 2010.

Examples (INPUT -> OUTPUT):

"12345" -> {"12345","1234","123","12","1"}

"6524" -> {"6524","652","65","6"}

"1" -> {"1"}

Order of output is important to me, but not necessary as an answer to this question, since there are sorting formulae available that I can apply to the output afterwards.

Bad input (such as empty strings, error values, or otherwise) should return an empty array, some error value, or other equally obvious output to show the formula failed due to bad input.

Must work for variable length input strings. I will not accept an answer that assumes fixed length input.

Must be an Excel formula (hence the excel-formula tag) and not rely on VBA macros. I could write a macro that does this, but for my purposes I need a formula.

Must work in Excel 2010 (hence the excel-2010 tag).

Alternatives

If you know how to generate output like this:

"12345" -> {5,4,3,2,1}

"6524" -> {4,3,2,1}

"1" -> {1}

Or even just:

"12345" -> {1,1,1,1,1}

"6524" -> {1,1,1,1}

"1" -> {1}

That would help greatly as well, since I believe those could be used as intermediate steps to getting to a solution. It's the variable length output array based on input string length that has me stuck.

Upvotes: 0

Views: 653

Answers (1)

Tim Williams
Tim Williams

Reputation: 166511

If your value is in A4 

=TRANSPOSE(MID(A4,1,ROW(OFFSET($A$1,0,0,LEN(A4),1))))

will give you an array of all substrings of length 1 to the full number.

In VBA:

Sub tester()

Dim v, x

v = Application.Evaluate("=TRANSPOSE(MID(A4,1,ROW(OFFSET($A$1,0,0,LEN(A4),1))))")

For x = LBound(v) To UBound(v)
    Debug.Print x, v(x)
Next x


End Sub

Upvotes: 4

Related Questions