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!

Thread Tools
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

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