Return the value of cell which is not blank and if all the cells are blank return UNT

**dinesh.sarsar** · 2015-02-23, 05:19 AM

Hi,

I have a file where the data can either be in column A, B, or C coloumn, so if there is any text in coloumn A, B, C return the value of that text, and if there is no data in A, B & C then return UNT.
Non Blank Cell.xlsx Non Blank Cell.xlsx

Please help.

**peter.abing** · 2015-02-23, 07:16 AM

Originally Posted by dinesh.sarsar

Hi,

I have a file where the data can either be in column A, B, or C coloumn, so if there is any text in coloumn A, B, C return the value of that text, and if there is no data in A, B & C then return UNT.
Non Blank Cell.xlsx Non Blank Cell.xlsx

Please help.

Hi,

Question:
1.) If more than 1 cells have values? What will be returned? What is the order of preference? (Example: Columns A and C will both contain different values, should the formula return the value in A or in C or both separated with a comma or other separator..
2.) Is the number of columns constant? Is there possibility that a column will be added and will be included in the formula?

This will help us help you.

Regards,
Peter

**sambit** · 2015-02-23, 07:25 AM

Hi dinesh,
Try below formula

=IF(ISTEXT(A2),A2,IF(ISTEXT(B2),B2,IF(ISTEXT(C2),C2,"UNT")))

**dinesh.sarsar** · 2015-02-23, 04:26 PM

Thanks a lot sambit, your suggested formula works

**Lester** · 2017-12-21, 03:44 AM

Originally Posted by sambit

Hi dinesh,
Try below formula

=IF(ISTEXT(A2),A2,IF(ISTEXT(B2),B2,IF(ISTEXT(C2),C2,"UNT")))

what if you want to step through rows from inside a macro. that is, keep incrementing the variable "row" by 1 to see if there is text in column 1 for the row span?

=ISTEXT(row, 1)

nor this below to convert the (row,1) to a proper reference address....

=ISTEXT(INDIRECT(ADDRESS(row,1)))