Display date above the left-most cell that contains a certain letter/number combination?

[ntech]

This is an example of a formula I would like to house in the AC4 cell that will display the date above the left-most cell that contains:

Any number from 0-999

Any number/”C” combination such as:

0-9C
10-99C
100-999C


I would like the formula to exclude the “CO” code and any other character only combinations.

 

[Peter_SSs]

This is an example of a formula I would like to house in the AC4 cell that will display the date above the left-most cell that contains:

Any number from 0-999

If cell H4 was changed to 50C would the result still be 3/2 as that cell does "contain" a number 0-999 or do you mean the cell must only contain a number 0-999?

Is it possible for a cell to contain numbers greater than 999? That is, could a cell be "2356" or "1234C"?

BTW, you will get many more potential helpers if you can post your sample data in a form that can be copied to our worksheets for testing. We can't do that from an image, and most helpers are not interested in manually typing out the sample data. There is a link in my signature block below with more on that.

 

[ntech]

There will not be any four digit numbers before the C. I was hoping to cover the range from 0-999C.

If cell H4 was changed to 50C would the result still be 3/2 as that cell does "contain" a number 0-999 or do you mean the cell must only contain a number 0-999?

Is it possible for a cell to contain numbers greater than 999? That is, could a cell be "2356" or "1234C"?

BTW, you will get many more potential helpers if you can post your sample data in a form that can be copied to our worksheets for testing. We can't do that from an image, and most helpers are not interested in manually typing out the sample data. There is a link in my signature block below with more on that.

 

[ntech]

Excel 2011 32 bit

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1
2							3/1	3/2	3/3	3/4	3/5	3/6	3/7	3/8	3/9	3/9	3/9	3/9		Start
3
4							X	5	5C	o	CO	MT	REV	50C	500C	100	35	SST		3/2

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

This is an example of a formula I would like to house in the T4 cell that will display the date above the left-most cell that contains:

Any number from 0-999

Any number/”C” combination

Examples

9C
56C
102C

I would like the formula to exclude the “CO” code and any other character only combinations.

 

[Peter_SSs]

There will not be any four digit numbers before the C. I was hoping to cover the range from 0-999C.

That didn't answer my first question.


.. but good to see the HTML maker working.

 

[ntech]

1. If cell H4 was changed to 50C would the result still be 3/2 as that cell does "contain" a number 0-999 or do you mean the cell must only contain a number 0-999?

Yes, the result would still be 3/2 because the formula needs to count a number and a number ending in C combination.

2. Is it possible for a cell to contain numbers greater than 999? That is, could a cell be "2356" or "1234C"?

No, the maximum numerical count would be 999 or 999C.

That didn't answer my first question.


.. but good to see the HTML maker working.

 

[Peter_SSs]

Yes, the result would still be 3/2 because the formula needs to count a number and a number ending in C combination.

Hmm, now I'm a bit more confused actually. I thought that these were two separate questions.

I would like to house in the AC4 cell that will display the date above the left-most cell that contains:

Any number from 0-999

Any number/”C” combination

Your original screen shot showed the result 3/2 in cell AC4 but column H did not contain a number/C value.

Do you really only have one question which is to show the first heading for a cell that contains only digits OR a digits/C combination?

 

[ntech]

That is correct, one question: formula looks for both digits or digits/c in the left-most part of the row, then displays the date in the heading above the identified cell.

Hmm, now I'm a bit more confused actually. I thought that these were two separate questions.


Your original screen shot showed the result 3/2 in cell AC4 but column H did not contain a number/C value.

Do you really only have one question which is to show the first heading for a cell that contains only digits OR a digits/C combination?

 

[Aladin Akyurek]

Perhaps... Control+shift+enter, not just enter:

=IFERROR(INDEX($G$2:$R$2,MATCH(TRUE,ISNUMBER(SUBSTITUTE(G4:R4,"C","")+0),0)),"does not exist")

 

[ntech]

That works as intended, however the only caveat is it will not count a lower case "number/c"

For example, it will count 25C but will skip 25c.

Is it possible to redesign the formula to include the lower case c in number combination in case the user is not consistent with capitalization?

Perhaps... Control+shift+enter, not just enter:

=IFERROR(INDEX($G$2:$R$2,MATCH(TRUE,ISNUMBER(SUBSTITUTE(G4:R4,"C","")+0),0)),"does not exist")