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

N

ntech

New Member
Joined
Oct 7, 2017
Messages
21
41v8uoA.jpg


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.
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes
ntech said:
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
Click to expand...
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.
 
Upvote 0
There will not be any four digit numbers before the C. I was hoping to cover the range from 0-999C.


Peter_SSs said:
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.
Click to expand...
 
Last edited:
Upvote 0
Excel 2011 32 bit
ABCDEFGHIJKLMNOPQRST

<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>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]3/1[/TD]
[TD="align: center"]3/2[/TD]
[TD="align: center"]3/3[/TD]
[TD="align: center"]3/4[/TD]
[TD="align: center"]3/5[/TD]
[TD="align: center"]3/6[/TD]
[TD="align: center"]3/7[/TD]
[TD="align: center"]3/8[/TD]
[TD="align: center"]3/9[/TD]
[TD="align: center"]3/9[/TD]
[TD="align: center"]3/9[/TD]
[TD="align: center"]3/9[/TD]
[TD="align: right"][/TD]
[TD="align: center"]Start[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]5C[/TD]
[TD="align: center"]o[/TD]
[TD="align: center"]CO[/TD]
[TD="align: center"]MT[/TD]
[TD="align: center"]REV[/TD]
[TD="align: center"]50C[/TD]
[TD="align: center"]500C[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]35[/TD]
[TD="align: center"]SST[/TD]
[TD="align: right"][/TD]
[TD="align: center"]3/2[/TD]

</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.
 
Upvote 0
ntech said:
There will not be any four digit numbers before the C. I was hoping to cover the range from 0-999C.
Click to expand...
That didn't answer my first question. :(


.. but good to see the HTML maker working. :)
 
Last edited:
Upvote 0

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.

Peter_SSs said:
That didn't answer my first question. :(


.. but good to see the HTML maker working. :)
Click to expand...
 
Upvote 0
ntech said:
Yes, the result would still be 3/2 because the formula needs to count a number and a number ending in C combination.
Click to expand...
Hmm, now I'm a bit more confused actually. I thought that these were two separate questions.
ntech said:
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
Click to expand...

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?
 
Upvote 0
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.

Peter_SSs said:
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?
Click to expand...
 
Last edited:
Upvote 0
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")
 
Upvote 0
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?

Aladin Akyurek said:
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")
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

Z
  • Question Question
Countdown date
Replies
2
Views
63
Zatman
Z
B
  • Solved
VLOOKUP issue with wildcard parameter for partial matches in number/letter combinations
2
Replies
10
Views
923
BrooksProctor
B
D
  • Question Question
How to keep a tally of matching letters in separate cells
Replies
4
Views
666
Davenil
D
B
  • Question Question
VBA to edit/update cells within a large table from a common "Dashboard"
Replies
1
Views
503
brianv
B
A
  • Solved
If a cell contains the word "Completed", then return the date/time when it changed.
2
Replies
14
Views
855
dreid1011
dreid1011

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top