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

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

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.xlsxNon Blank Cell.xlsx  Reply With Quote

2. 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.xlsxNon Blank Cell.xlsx

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?

Regards,
Peter  Reply With Quote

3. Hi dinesh,
Try below formula

=IF(ISTEXT(A2),A2,IF(ISTEXT(B2),B2,IF(ISTEXT(C2),C2,"UNT")))  Reply With Quote

4. Thanks a lot sambit, your suggested formula works  Reply With Quote

5. ## how would tou step through rows with this? 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....  Reply With Quote

6. Lester - the thread is two years old.   Reply With Quote

7. LOL, I didn't notice that.  Reply With Quote

text data 