HELP! - Need Updated logic and I'm not sure how to do it

[BishopDesigns]

I currently have logic in excel that is structured like this -

=IFERROR(INDEX($A$1:$A$1000000, SMALL(IF(LEFT($A$1:$A$1000000,1)="T", ROW($A$1:$A$1000000)-MIN(ROW($A$1:$A$1000000))+1, ""), ROW(1:1))), "")
Basically, it looks at Column A and Identifies every Row that begins with the letter "T" and lists it.

The data listed in Column A has now changed... Every single Row Starts with an "N" followed by a number in sequence and a space before you get to the letter "T"
The "N" value can be anything from "N10 " to N1000010 "

How do I get my logic to ignore the "N" numbers and continue only presenting me with the "T" values?

The "T" values could be anything from T1M6" to T200M6"

Here are some Examples:

N362 T15M06 N204899 T130M06 N60 T5M06

 

[dreid1011]

Change your LEFT() to MID() perhaps. Maybe like this:

MID($A$1:$A$1000000,FIND(" ",$A$1:$A$1000000)+1,1)

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[dreid1011]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Yes, please let us know what version of excel. If you are using 365, your formula can be greatly reduced. To something like this perhaps:

=FILTER(A1:A1000000,IFERROR(SEARCH(" T",A1:A1000000,),"")<>"")

Or even:

=LET(r,A1:A1000000,FILTER(r,IFERROR(SEARCH(" T",r,),"")<>""))

 

[BishopDesigns]

 

[dreid1011]

View attachment 102626View attachment 102627View attachment 102628View attachment 102629

The #SPILL! error is because you are dragging the formula down. You only need to put it in the top most cell. Also, I did not mean for you to replace the entire formula with MID(). I meant for you to replace only the LEFT() portion of your original formula. Like this:

=IFERROR(INDEX($A$1:$A$1000000, SMALL(IF(MID($A$1:$A$1000000,FIND(" ",$A$1:$A$1000000)+1,1)="T", ROW($A$1:$A$1000000)-MIN(ROW($A$1:$A$1000000))+1, ""), ROW(1:1))), "")

 

[dreid1011]

Edit - what does the #CALC! error message say when you mouse over it?

Book1 11-27-2023.xlsx
	A	B
1	%	N362 T15M06
2	O00001	N204899 T130M06
3	( MATERIAL SETUP=0.321 )	N60 T5M06
4	N10
5	N11
6	N12
7	N13 ( )
8	N14 G00 G40 G53 G80 G90
9	N15 M01
10	N16 ( ----------------------------------)
11	N362 T15M06
12	N204899 T130M06
13	N60 T5M06
14	N75 Q3M05
Sheet2

Cell Formulas
Range	Formula
B1:B3	B1	=FILTER(A1:A14,IFERROR(SEARCH(" T",A1:A14),"")<>"")
Dynamic array formulas.