Return the right-most cell in a row that contains a number in front of a "c"

[ntech]

I would like to figure out how to write a formula that will find and display the right-most cell in a row that contains a number in front of a "c".

[/IMG]

Note, I wish for the "CO" to be excluded from the search.

 

[Peter_SSs]

Welcome to the MrExcel board!

Does this do what you want?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	98	99C	CO	X	100	100c	SS	101				100c
Right-most

Cell Formulas
Range	Formula
L1	=INDEX(A1:J1,AGGREGATE(14,6,(COLUMN(A1:J1)-COLUMN(A1)+1)/((RIGHT(A1:J1,1)="c")*ISNUMBER(LEFT(A1:J1,LEN(A1:J1)-1)+0)),1))

If you really only need to see if the cell ends in a "c", you could try this shorter version.

=INDEX(A1:J1,AGGREGATE(14,6,(COLUMN(A1:J1)-COLUMN(A1)+1)/(RIGHT(A1:J1,1)="c"),1))

 

[Tetra201]

Or this array formula (to be entered using Ctrl+Shift+Enter, not just Enter):

=INDEX(A1:H1,MAX(IF((RIGHT(A1:H1)="c")*ISNUMBER(--REPLACE(A1:H1,LEN(A1:H1),1,"")),COLUMN(A1:H1)-COLUMN(A1)+1)))

 

[ntech]

Thank you! That works wonderfully!

Welcome to the MrExcel board!

Does this do what you want?

	A	B	C	D	E	F	G	H	I	J	K	L
99C	CO	X	100c	SS	100c

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]98[/TD]

[TD="align: right"]100[/TD]

[TD="align: right"]101[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Right-most​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]L1[/TH]
[TD="align: left"]=INDEX(A1:J1,AGGREGATE(14,6,(COLUMN(A1:J1)-COLUMN(A1)+1)/((RIGHT(A1:J1,1)="c")*ISNUMBER(LEFT(A1:J1,LEN(A1:J1)-1)+0)),1))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]




If you really only need to see if the cell ends in a "c", you could try this shorter version.

=INDEX(A1:J1,AGGREGATE(14,6,(COLUMN(A1:J1)-COLUMN(A1)+1)/(RIGHT(A1:J1,1)="c"),1))

 

[Peter_SSs]

Thank you! That works wonderfully!

Good news.
I should just point out that my formula (& Tetra's) could fail if the cells could contain values like "12DECC" or "1E5C" but I took a guess that would not be possible. If it is, we would need to re-think the suggestion.

 

[ntech]

That will not be an issue. The various codes will consist of one, two, and three-digit numbers preceding the C. All other entries will be numerical only, or characters only.

 

[Peter_SSs]

That will not be an issue. The various codes will consist of one, two, and three-digit numbers preceding the C. All other entries will be numerical only, or characters only.

In that case you could go with a slightly simpler version if your want:
=INDEX(A1:J1,AGGREGATE(14,6,(COLUMN(A1:J1)-COLUMN(A1)+1)/((RIGHT(A1:J1,1)="c")*ISNUMBER(LEFT(A1:J1,1)+0)),1))