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.
View attachment Non Blank Cell.xlsxView attachment Non Blank Cell.xlsx

Please help.

dinesh.sarsar said:

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.
View attachment 3204View attachment 3204

Please help.

Click to expand...

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

Hi dinesh,
Try below formula

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

Thanks a lot sambit, your suggested formula works

how would tou step through rows with this?

sambit said:

Hi dinesh,
Try below formula

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

Click to expand...

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)))

Lester - the thread is two years old.

LOL, I didn't notice that.