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.

 

[Peter_SSs]

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))

Then this is a Google Sheets question, not Excel?

 

[mistah_q]

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.

Thanks, I've done this now.

 

[mistah_q]

Then this is a Google Sheets question, not Excel?

Excel 2019

 

[Fluff]

If you are using Excel why did you post a formula using ArrayFormula & say it was the solution, when that function does not exist in Excel?

 

[mistah_q]

I use both, but I specifically need it to be working in Excel 2019. "=ArrayFormula" is basically the same thing as confirming with ctrl+shift+enter. Since this is the Excel forum let me amend my above solution to finding the position of the first letter in an alphanumeric string:

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)

Confirmed with ctrl+shif+enter.

Anyway I found a solution to the last part too which works in Excel 2019 - finding the position of the last letter in an alphanumeric string

IF(LEN(A5)=0,0,MATCH(2,1/(ABS((CODE(UPPER(MID(A5,ROW(INDIRECT("1:"&LEN(A5))),1)))-77.5))<=12.5)))

 

[Peter_SSs]

Would these work for you?

23 01 25.xlsm
	A	B	C	D
1	373Bags of corn1119	373	Bags of corn	1119
2	2Barracudas10	2	Barracudas	10
3	64Hides128	64	Hides	128
4	5Curative compounds "Cure1919"5	5	Curative compounds "Cure1919"	5
5	17Carts680	17	Carts	680
6	8Knives24	8	Knives	24
7	37.3Bags of corn11.19	37.3	Bags of corn	11.19
Split Text

Cell Formulas
Range	Formula
B1:B7	B1	=LEFT(A1,MATCH(FALSE,ISNUMBER((0&MID(A1,{1,2,3,4,5,6,7,8,9},1))+0),0)-1)+0
C1:C7	C1	=MID(A1,LEN(B1)+1,LEN(A1)-LEN(B1&D1))
D1:D7	D1	=RIGHT(A1,LEN(AGGREGATE(14,6,RIGHT(SUBSTITUTE(A1,".",1),{1,2,3,4,5,6,7,8,9})+0,1)))+0