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]

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

As noted, the OP seems happy with the worksheet formula suggestion, but if we are investigating UDFs, then another one might be

Function FirstOne(rng As Range)
  FirstOne = rng.Find("?*", rng.Cells(rng.Cells.Count), xlValues, , xlPrevious).Value
End Function

Comments:
- This, like crazydragon's but unlike Rick's, would work on a single column range as well as a single row range. ('First' in a multi-row or multi-column range is meaningless without further definition of what 'First' means).
- This, like the worksheet functions, returns an error if there are no values in the range. To me, that is a more informative result than 0 (crazydragon) or null string (Rick)
- This, like crazydragon's but unlike Rick's, preserves a numerical value as such (rather than converting it to a string) & also preserves multi-word values.