Results 1 to 7 of 7

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

  1. #1

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

    Please help.

  2. #2
    Quote Originally Posted by dinesh.sarsar View Post
    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

    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

  3. #3
    Conjurer sambit's Avatar
    Join Date
    Sep 2014
    Posts
    143
    Articles
    0
    Excel Version
    Excel2007
    Hi dinesh,
    Try below formula

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

  4. #4
    Thanks a lot sambit, your suggested formula works

  5. #5
    Seeker Lester's Avatar
    Join Date
    Sep 2017
    Location
    Texas
    Posts
    7
    Articles
    0

    how would tou step through rows with this?

    Quote Originally Posted by sambit View Post
    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)))

  6. #6
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,471
    Articles
    0
    Excel Version
    Office 365 Subscription
    Lester - the thread is two years old.
    Ali
    Enthusiastic self-taught user of MS Excel!

  7. #7
    Seeker Lester's Avatar
    Join Date
    Sep 2017
    Location
    Texas
    Posts
    7
    Articles
    0
    LOL, I didn't notice that.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •