Min Array of Capital Letters?

[mistah_q]

For example, in A4 I have this:

373Bags of corn1119

And in A5 I have this:

=MIN(FIND({CHAR(SEQUENCE(1,26,65))},A4&"ABCDEFGHIJKLMNOPQRSTUVWXYZ"))

I'm expecting A5 to show me the answer "4" but it's showing me "20" so it isn't seeing the B in "Bags of Corn" and is skipping straight to the error-prevention array at the end.

What am I missing?

________________________________

While I'm at it, I can ask if maybe there's a more elegant way for what I'm trying to do. I have, for example, this list:

373Bags of corn1119

2Barracudas10

64Hides128

5Curative compounds "Cure1919"5

17Carts680

8Knives24

And I want a set of formulas that will nicely cut it into three columns:

373	Bags of corn	1119
2	Barracutas	10
64	Hides	128
5	Curative compounds "Cure1919"	5
17	Carts	680
8	Knives	24

The tailing number isn't important, I don't mind hacking it off. The preceding number is. And since there's a number on each side I can't really use left/mid/right formulae.

I thought using a letter array to locate the first non-number and thereby separate the preceding number first would be an elegant solution. If I could just get that to show me a "4" I could use left and right to pick the rest apart effortlessly, but I can't seem to get it going.

 

[jasonb75]

Shouldn't it be SEQUENCE(65,,26)

Also, take the array brackets out of the formula, they're invalid where you have them.

 

[mistah_q]

Best practice, you're right, removed the array brackets. I originally had them because I tried to do {"A","B","C","and-so-on"} but that was also giving me "20" so I tried using SEQUENCE.

SEQUENCE(65,,26) just yields an error message.

 

[52779]

Hi,
according to your formula
(array brackets only work with values)

=MIN(FIND(CHAR(SEQUENCE(1,26,65)),A4&"ABCDEFGHIJKLMNOPQRSTUVWXYZ"))

The following formula can also be used

=MIN(IFERROR(FIND(BASE(SEQUENCE(26,,10),36),A4),""))

 

[52779]

One way
(For your 2nd question)

(The absence of numbers at the beginning and at the end was also evaluated.)

=LET(a,A1:A6,s,SEQUENCE(MAX(LEN(A1:A6))),DROP(REDUCE(0,a,LAMBDA(x,y,LET(c,IFNA(SUBSTITUTE(LOOKUP(9^99,--LEFT(y,s)),",","."),""),d,IFNA(SUBSTITUTE(LOOKUP(9^99,--RIGHT(y,s)),",","."),""),e,IF(c="",0,LEN(c)),f,IF(d="",0,LEN(d)),g,MID(y,e+1,LEN(y)-(e+f)),VSTACK(x,HSTACK(c,g,d))))),1))

 

[52779]

in my post above
The SUBSTITUTE function is for arrangement of the decimal seperator.
If not required, the formula can be used as follows.

=LET(a,A1:A6,s,SEQUENCE(MAX(LEN(A1:A6))),DROP(REDUCE(0,a,LAMBDA(x,y,LET(c,IFNA(LOOKUP(9^99,--LEFT(y,s)),""),d,IFNA(LOOKUP(9^99,--RIGHT(y,s)),""),e,IF(c="",0,LEN(c)),f,IF(d="",0,LEN(d)),g,MID(y,e+1,LEN(y)-(e+f)),VSTACK(x,HSTACK(c,g,d))))),1))

 

[mistah_q]

Thanks for the attempt. The solution you offered for the A4 cell is returning a NUM error.

And I'm assuming your other solution works fine but I don't have the most recent version of Excel, so I can't use the LET function it seems.

I worked out a solution though. I found a way to find the position of the first non-number value in a cell:

=ArrayFormula(IF(LEN(A4)=0,0,MIN(IF(1*ISNUMBER(1*MID(A4,ROW(INDIRECT("A1:A"& LEN(A4))),1))=0,ROW(INDIRECT("A1:A"&LEN(A4))),LEN(A4)+1)))* (ISNUMBER(A4)=FALSE))

So I can break off the first number with a left/right call from there.

Now all I have to do is work out a formula to find the position of the "last non-number value." Using the above but changing MIN to MAX doesn't do it... it just returns the position of the absolute last character in the cell (regardless of whether it's a number).

 

[Rick Rothstein]

If you don't mind a formula that spills across but must be copied down, then give this a try. Assuming your list starts in cell A1, put this formula in cell B1 (it will spill across to cell D1) and copy it down to the end of your data...

=LET(a,A1,s,SEQUENCE(99),b,LOOKUP(9E+99,0+LEFT(a,s)),d,0+RIGHT(a,COUNT(,-RIGHT(a,s))-1),c,MID(a,LEN(b)+1,LEN(a)-LEN(b)-LEN(d)),HSTACK(b,c,d))

 

[mistah_q]

That's a good shot, probably works fantastic! But I can't use the LET function, don't have a modern enough version of Excel going.

 

[Rick Rothstein]

That's a good shot, probably works fantastic! But I can't use the LET function, don't have a modern enough version of Excel going.

Why don't you update your profile on this forum and indicate what version of Excel you are using, then we would know how to tailor our answers for your particular needs on this and future questions you might ask. To do this, click the "button" at the top of this window with your picture in it... click "Account Details" in the dialog box that appears, scroll down till you see the checkboxes for the various versions of Excel and check all that apply to you... make sure you click the Save button at the bottom.