Look for text in column C to Z and place the single instance in B

[ThumperStrauss]

I have a sheet with a 26 columns. Column A is the name of a street. Columns C to Z are all empty, except for one, which contains the house number. The column with the house number could be in any one of the columns. So, my question is this: How can I get Excel to look at the values of C1 to Z1, and when it find the single cell with the value, place that value in B1 and stop looking?

[alansidman]

Please read the yellow banner at the top of this page on how to attach a file.

[Rick Rothstein]

Try the MAX function...

=MAX(C1:Z1)

[ThumperStrauss]

Thank you! It works.

[ThumperStrauss]

Update: While the =MAX() works for simple numbers, it doesn't for numbers with apartment numbers preceding the house numbers. So if 123 Wilson Street, Apt. 1101 is listed as 1101-123, then the =MAX() yields a zero for that item. Is there another possible solution?

[Rick Rothstein]

Give this formula a try...

=LOOKUP(2,1/(C1:Z1<>""),C1:Z1)