Simple Question for an Expert

[BrettOlbrys1]

I know this is probably simple, but I am having trouble. I simply want to return the first value in a row that is not blank. So if X=blank:

X X 4 X 5 6 1 X 5
X 3 X 5 X X X X X

For the 1st row, the returned value would be 4
For the 2nd row, the returned value would be 3

What is the formula I need to use to return the 1st non-blank value in a row?

Thanks

 

[Peter_SSs]

Welcome to the MrExcel board!

A couple of choices.
- The column K formula confirmed with Ctrl+Shift+Enter, not just Enter then copied down, or
- The column L formula that does not require the special confirmation.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1			4		5	6	1		5		4	4
2		3		5							3	3
First non-blank

 

[crazydragon84]

you can do a vba function and use that

in vba editor, put in this function:

function find_first(Search_Rng as Range)
Dim rng as Range

For each rng in Search_Rng
     If rng <>"" then
           find_first = rng.value
           exit function
     end if
next rng

End function

Then, in your spreadsheet, just type in formula =find_First(Range you want to apply) and this should return the first value in that range that's not blank.

 

[BrettOlbrys1]

Worked perfectly, thank you.

Welcome to the MrExcel board!

A couple of choices.
- The column K formula confirmed with Ctrl+Shift+Enter, not just Enter then copied down, or
- The column L formula that does not require the special confirmation.

First non-blank

	A	B	C	D	E	F	G	H	I	J	K	L
1			4		5	6	1		5		4	4
2		3		5							3	3

<tbody>
</tbody>

Spreadsheet Formulas

Cell Formula K1 {=INDEX(A1:I1,MATCH(TRUE,A1:I1<>"",0))} L1 =INDEX(A1:I1,MATCH(TRUE,INDEX(A1:I1<>"",0),0)) <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Peter_SSs]

Worked perfectly, thank you.

You're welcome.

 

[Rick Rothstein]

you can do a vba function and use that

in vba editor, put in this function:

function find_first(Search_Rng as Range)
Dim rng as Range

For each rng in Search_Rng
     If rng <>"" then
           find_first = rng.value
           exit function
     end if
next rng

End function

The OP apparently wanted a formula solution, but I thought you might find the following alternate UDF, a one-liner, to be of interest...

[table="width: 500"]
[tr]
	[td]Function FindFirst(rng As Range)
  FindFirst = Split(Application.Trim(Join(Application.Index(rng.Value, 1, 0))) & " ")(0)
End Function[/td]
[/tr]
[/table]

 

[Peter_SSs]

The OP apparently wanted a formula solution, but I thought you might find the following alternate UDF, a one-liner, to be of interest...

[table="width: 500"]
[tr]
	[td]Function FindFirst(rng As Range)
  FindFirst = Split(Application.Trim(Join(Application.Index(rng.Value, 1, 0)))[COLOR="#FF0000"][B] & " "[/B][/COLOR])(0)
End Function[/td]
[/tr]
[/table]

Is there any need for the red text?
Have to hope the first non-blank cells doesn't contain a space character.

 

[Rick Rothstein]

Is there any need for the red text?

It makes the function print out the empty string ("") if the whole row is empty (otherwise the function prints out a #VALUE! error).

Have to hope the first non-blank cells doesn't contain a space character.

Why?

 

[Peter_SSs]

It makes the function print out the empty string ("") if the whole row is empty (otherwise the function prints out a #VALUE ! error).

Fair enough, though an error may be preferred - as the formula suggestions that were accepted produce an error in that circumstance.

Why?

Because if the first non-empty cell contains "The cat sat on the mat" the function as-is returns "The", rather than the requested "the first value in a row that is not blank".
I do note that the sample data was purely numeric so the situation might not arise for the OP, but made the comment in case the data might be varied or another reader might wish to use the function in other circumstances.

 

[crazydragon84]

The OP apparently wanted a formula solution, but I thought you might find the following alternate UDF, a one-liner, to be of interest...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Function FindFirst(rng As Range)
  FindFirst = Split(Application.Trim(Join(Application.Index(rng.Value, 1, 0))) & " ")(0)
End Function[/TD]
[/TR]
</tbody>[/TABLE]

That's interesting. I'll have to look at what it's doing. Thanks