Get First & Last Numeric or Text Value in a Sparse Row

First and last mean position, not size or an inferred date

The range B2:G2 is ordered from left to right. The first and last readings are chronological only if the columns were already arranged chronologically. MIN and MAX answer a different question: smallest and largest value.

ISNUMBER includes zero and numeric date serials, but excludes text such as a formula returning "123". ISTEXT selects text; this example additionally excludes empty text from formulas returning "". A space-only string remains text, because no trimming is applied.

Resolve source-cell errors before using these examples. The text mask compares each source cell with empty text; an error in that comparison can propagate. The formulas are not intended to hide an error as a missing observation.

First numeric value

INDEX explicitly selects row1 and column1 of the horizontal filtered result.

=IF(COUNT(B2:G2)=0,"No Numbers",INDEX(FILTER(B2:G2,ISNUMBER(B2:G2)),1,1))

Last numeric value

TAKE selects one row and the last column. With no numbers, return No Numbers.

=IF(COUNT(B2:G2)=0,"No Numbers",TAKE(FILTER(B2:G2,ISNUMBER(B2:G2)),1,-1))

First nonempty text entry

The FILTER fallback supplies No Text when no nonempty text remains.

=INDEX(FILTER(B2:G2,ISTEXT(B2:G2)*(B2:G2<>""),"No Text"),1,1)

Last nonempty text entry

Returns the rightmost nonempty text under the same policy.

=TAKE(FILTER(B2:G2,ISTEXT(B2:G2)*(B2:G2<>""),"No Text"),1,-1)

Further reading

• Microsoft Support — FILTER function
• Microsoft Support — INDEX function row and column arguments