Finding the nth Blank Value in a Row of Text

ljg713 · #1 01-11-2016, 07:28 AM

Quote:

Originally Posted by xor

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))))

Thanks for the reply! However, none of these equations works for me. The first one allows me to enter the formula, but the cell is blank, whereas I would like a formula that when entered in four consecutive cells gives me the first four nonblank text cells within the range I specified.

The second column won't allow me to enter it all due to some sort of error :/

ljg713 · #2 01-11-2016, 07:50 AM

I decided that my original answer works well, except for one issue.

=IF(COUNTIF(E1:H1,"?*")>=2,INDEX(E1:H1,SMALL(IF(E1 :H1<>"",COLUMN(E1:H1)-COLUMN(E1)+1),1)),"") (for the first nonblank)

gives me what I want, but it needs to return a blank if there is no text to be returned (for example, if a particular row only has 3 non-blank text cells, it needs to still work for the 4th non-blank but just return "" so it appears blank). Instead, in the cases where there isn't any text to be returned, I get a #NUM! error.

Any tips for how to get rid of this error and replace it with empty text like ""?

Thanks!

xor · #3 01-11-2016, 08:46 AM

My formulas work fine.

ljg713 · #4 01-12-2016, 07:49 AM

Thank you!

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
finding a string containing a combination of formats and adding text before	Marco	Word VBA	1	11-02-2015 05:08 PM
Finding and Replacing a word with text copied to clip board	spc94	Word VBA	3	06-25-2015 04:46 AM
Finding varying text in an MS Word file	sergiodcq	Word	1	07-08-2014 03:12 AM
Finding specific text within body of email	Tammfran	Outlook	0	03-14-2014 02:32 PM
Finding a text style in a cell	jimbassett	Excel	1	02-10-2010 08:39 PM