For example, in A4 I have this:
And in A5 I have this:
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:
And I want a set of formulas that will nicely cut it into three columns:
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.
373Bags of corn1119
And in A5 I have this:
Excel Formula:
=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.