#1
|
|||
|
|||
Finding the nth Blank Value in a Row of Text
I have a row of text (not sure if this is relevant, but the text isn't purely typed, it is a formula of concatenations and if statements).
Some cells appear blank (although they still have the same ifelse/concatenation formula, but end up being "") So, basically I have a row of formulaic text that looks like this A1 B1 C1 D1 E1 F1 G1 H1 I1 J1 I want to use the range E1 through H1, and write a formula in a different worksheet that will order these by non-blank cells. So, I need to find the first nonblank cell, 2nd nonblank cell, 3rd, and 4th. Because some of these cells are blank, not all 4 of the spots will be fill, but the formula needs to make sure that if there is text in all of them then it will fill in order. =IF(COUNTIF(E1:H1,"?*")>=2,INDEX(E1:H1,SMALL(IF(E1 :H1<>"",COLUMN(E1:H1)-COLUMN(E1)+1),1)),"") (for the first nonblank) =IF(COUNTIF(E1:H1,"?*")>=2,INDEX(E1:H1,SMALL(IF(E1 :H1<>"",COLUMN(E1:H1)-COLUMN(E1)+1),2)),"") (for the 2nd nonblank), etc. I have been trying this array formula, and it works for finding the first two non-blank text values, but when I try to find the 3rd and 4th nonblank values, if there is a blank value, instead of just a blank I am getting #NUM!. Any ideas? |
#2
|
||||
|
||||
Just to be sure I understand, a blank cell is always a cell containing a null text string ? ("")
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#3
|
|||
|
|||
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)))) |
#4
|
|||
|
|||
Yep, that is Correct.
|
#5
|
|||
|
|||
Quote:
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 :/ |
#6
|
|||
|
|||
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! |
#7
|
|||
|
|||
Finding the nth Blank Value in a Row of Text
My formulas work fine.
|
#8
|
|||
|
|||
Thank you!
|
Tags |
formula help |
Thread Tools | |
Display Modes | |
|
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 |