find first cell containing text in a range

[Rubber Beaked Woodpecker]

Hi all

If possible I'm hoping someone can kindly provide a formula for the following please;

In Z1 I would like a formula to look at the range U6:AJ15 and return the text of the first cell that contains text.

Hope this makes sense

Thanks RBW

 

[hagia_sofia]

Hello, how is "first occurence" defined - row by row or column by column?

 

[Rubber Beaked Woodpecker]

Hello, how is "first occurence" defined - row by row or column by column?

Hi sorry for being so vague. column by column.

Thanks

 

[Cubist]

One option to try:

Book1
	T	U	V	W	X	Y	Z	AA
1							z	<-scan by row
2							x	<- scan by column
3
4
5
6					z
7		x		c	d
8			k	k
9
Sheet5

Cell Formulas
Range	Formula
Z1	Z1	=LET(t,U6:X8,s,SEQUENCE(ROWS(t),COLUMNS(t)),CONCAT(IF(s=MIN(IF((t<>""),s,"")),t,"")))
Z2	Z2	=LET(t,TRANSPOSE(U6:X8&""),s,SEQUENCE(ROWS(t),COLUMNS(t)),CONCAT(IF(s=MIN(IF(t<>"",s,"")),t,"")))

 

[Scott Huish]

For scanning by column, how about: =INDEX(TOCOL(U6:X8,1,TRUE),1)

For scanning by row: =INDEX(TOCOL(U6:X8,1),1)

 

[Cubist]

TOCOL is not available in XL 2021.

 

[Cubist]

It was for the 2021 web/online version only - I think.

TOCOL function - Microsoft Support

support.microsoft.com

 

[Scott Huish]

OK. I did try and look that up before I posted that solution.

 

[Rubber Beaked Woodpecker]

One option to try:

Book1
	T	U	V	W	X	Y	Z	AA
1							z	<-scan by row
2							x	<- scan by column
3
4
5
6					z
7		x		c	d
8			k	k
9
Sheet5

Cell Formulas
Range	Formula
Z1	Z1	=LET(t,U6:X8,s,SEQUENCE(ROWS(t),COLUMNS(t)),CONCAT(IF(s=MIN(IF((t<>""),s,"")),t,"")))
Z2	Z2	=LET(t,TRANSPOSE(U6:X8&""),s,SEQUENCE(ROWS(t),COLUMNS(t)),CONCAT(IF(s=MIN(IF(t<>"",s,"")),t,"")))

Works perfectly thank you

 

[HighAndWilder]

Hi all

If possible I'm hoping someone can kindly provide a formula for the following please;

In Z1 I would like a formula to look at the range U6:AJ15 and return the text of the first cell that contains text.

Hope this makes sense

Thanks RBW

Does this work for you?

Compare two ranges in Excel to see if they match exactly.xlsm
	T	U	V	W	X	Y	Z	AA	AB	AC	AD
1							z	<-scan by row
2							x	<- scan by column
3
4
5
6					z
7		x		c	d
8			k	k
9
10
Sheet2

Cell Formulas
Range	Formula
Z1	Z1	=LEFT(TEXTJOIN("",TRUE,U6:X8),1)
Z2	Z2	=LEFT(TEXTJOIN("",TRUE,TRANSPOSE(IF(U6:X8="","",U6:X8))),1)