+ Reply to Thread
Results 1 to 5 of 5

Excel 2007 : Returning cell in range that contains text

  1. #1
    Registered User
    Join Date
    02-15-2012
    Location
    Ithaca
    MS-Off Ver
    Excel 2003
    Posts
    18

    Returning cell in range that contains text

    If I have a range of data from A1:F1 that is all blank except for E1 which has Axe, and I want to put a formula in G1 to search the range and return the text, how do I do it?

    What I tried originally was IF(A1:F1<>"", A1:F1, ""). I tried this as an array formula and it returned blank. When I put the text in A1, it returned the text. I am not too familiar with array formulae and what I am doing wrong.

    Eventually I want to apply this formula to multiple rows and in each row, the text may appear in a different column.

  2. #2
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2007, 2010
    Posts
    31,434

    Re: Returning cell in range that contains text

    =LOOKUP(REPT("z", 255), A1:F1) will return the value of the rightmost cell with text.
    Microsoft MVP - Excel
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Forum Expert DGagnon's Avatar
    Join Date
    02-23-2012
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2003, 2007
    Posts
    1,645

    Re: Returning cell in range that contains text

    Give this a try, as an array formula

    =INDEX(A1:F1,MIN(IF(A1:F1<>"",COLUMN(A1:F1))))

    it will return the left most cell with text
    If you liked my solution, please click on the Star -- to add to my reputation

    If your issue as been resolved, please clearly state so and mark the thread as [SOLVED] using the thread tools just above the first post.

  4. #4
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    wales
    MS-Off Ver
    Excel 2007
    Posts
    861

    Re: Returning cell in range that contains text

    I would probably do this, this is not an array formula

    =INDEX(A1:F1,1,MATCH(FALSE,INDEX(A1:F1="",,),0))

    how it works if you interested

    index(A1:F1="",,) creates a true or false value for each cell in that range based on if its empty (true) or contains info (false)

    match(false(above statement),0) returns the first value where the false statement occurs (value = 5 in this case)

    index(A1:F1,1,5 (from above) = plain old index returns the value of that cell

    I find an explanation also helps you to discover your own way to solve these issues.
    Can you solve the Bluecell? A new way to learn Excel http://excelbluecell.com/
    Visit my corner for tutorials and exercises http://excelbluecell.com/stevescorner/
    <--- If a post helps hit the star

  5. #5
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,454

    Re: Returning cell in range that contains text

    Or
    =INDEX(A1:F1,,MATCH("*",A1:F1,0))
    to return the leftmost text value

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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