Thread: [Solved] Finding the nth Blank Value in a Row of Text
View Single Post
 
Old 01-09-2016, 05:48 AM
xor xor is offline Windows 10 Office 2013
Expert
  
Join Date: Oct 2015
Posts: 1,100
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
Default
Name A1:J1 as d and use the matrix formula:

=IFERROR(INDEX(d,SMALL(IF(d<>"",COLUMN(d)-CELL("col",d)+1),COLUMNS($E:E))),"")

for listing the first non-blank text. Copy the formula to the right to list second, third etc. non-blank text.

If you do not want to use IFERROR, you can instead use:

=IF(COLUMNS($E:E)>SUM(--(d<>"")),"",INDEX(d,SMALL(IF(d<>"",COLUMN(d)-CELL("col",d)+1),COLUMNS($E:E))))
Reply With Quote