Search for specific text within last non blank cell in a range
 

Hi

In MS Excel 2007, I'm trying to use a function to search for (and display) a
specific text string that resides within a cell, providing that it is the
last non blank cell in a range of cells across a row.

e.g. If searching row 2:2 (or a section of this row) for the last non blank
cell, I want to then search within that last cell to locate the word "pass",
where the same cell may also contain any combination of text/number values.
If "pass" exists within the cell, I want to return a the same text value of
"pass". Note that €œpass€� may exist across various cells in the range.

-- I've managed to successfully return the entire contents that reside
within the last non-blank cell (with the following formula):

=IF(ISNA(LOOKUP(2,1/('QA Results'!$D2:$CY2<""),'QA
Results'!$D2:$CY2)),"",LOOKUP(2,1/('QA Results'!$D2:$CY2<""),'QA
Results'!$D2:$CY2))

-- I've also achieved similar results when searching the cell range for the
last known cell that successfully contains the text €œpass€� (this is not
necessarily the last non blank cell in the range). Again the entire cell
content is returned.

=IF(OR(ISNA('QA Results'!D2:CY2),ISERROR(LOOKUP(2,1/SEARCH("Pass",'QA
Results'!D2:CY2),'QA Results'!$D$2:$CY$2))),"",LOOKUP(2,1/SEARCH("Pass",'QA
Results'!D2:CY2),'QA Results'!$D$2:$CY$2))

Any assistance would be appreciated.

Search for specific text within last non blank cell in a range
 

If I understand the question then

=IF(ISNUMBER(FIND("pass",LOOKUP(2,1/(B4:L4<""),B4:L4))),"pass","")

would check the range B4:L4.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Bevo" wrote:

Hi

In MS Excel 2007, I'm trying to use a function to search for (and display) a
specific text string that resides within a cell, providing that it is the
last non blank cell in a range of cells across a row.

e.g. If searching row 2:2 (or a section of this row) for the last non blank
cell, I want to then search within that last cell to locate the word "pass",
where the same cell may also contain any combination of text/number values.
If "pass" exists within the cell, I want to return a the same text value of
"pass". Note that €œpass€� may exist across various cells in the range.

-- I've managed to successfully return the entire contents that reside
within the last non-blank cell (with the following formula):

=IF(ISNA(LOOKUP(2,1/('QA Results'!$D2:$CY2<""),'QA
Results'!$D2:$CY2)),"",LOOKUP(2,1/('QA Results'!$D2:$CY2<""),'QA
Results'!$D2:$CY2))

-- I've also achieved similar results when searching the cell range for the
last known cell that successfully contains the text €œpass€� (this is not
necessarily the last non blank cell in the range). Again the entire cell
content is returned.

=IF(OR(ISNA('QA Results'!D2:CY2),ISERROR(LOOKUP(2,1/SEARCH("Pass",'QA
Results'!D2:CY2),'QA Results'!$D$2:$CY$2))),"",LOOKUP(2,1/SEARCH("Pass",'QA
Results'!D2:CY2),'QA Results'!$D$2:$CY$2))

Any assistance would be appreciated.

Search for specific text within last non blank cell in a range
 

Appreciate the feedback Shane

What you've outlined makes perfect sense -- and is what I was after.

Cheers

Bevo



"Shane Devenshire" wrote:

If I understand the question then

=IF(ISNUMBER(FIND("pass",LOOKUP(2,1/(B4:L4<""),B4:L4))),"pass","")

would check the range B4:L4.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Bevo" wrote:

Hi

In MS Excel 2007, I'm trying to use a function to search for (and display) a
specific text string that resides within a cell, providing that it is the
last non blank cell in a range of cells across a row.

e.g. If searching row 2:2 (or a section of this row) for the last non blank
cell, I want to then search within that last cell to locate the word "pass",
where the same cell may also contain any combination of text/number values.
If "pass" exists within the cell, I want to return a the same text value of
"pass". Note that €œpass€� may exist across various cells in the range.

-- I've managed to successfully return the entire contents that reside
within the last non-blank cell (with the following formula):

=IF(ISNA(LOOKUP(2,1/('QA Results'!$D2:$CY2<""),'QA
Results'!$D2:$CY2)),"",LOOKUP(2,1/('QA Results'!$D2:$CY2<""),'QA
Results'!$D2:$CY2))

-- I've also achieved similar results when searching the cell range for the
last known cell that successfully contains the text €œpass€� (this is not
necessarily the last non blank cell in the range). Again the entire cell
content is returned.

=IF(OR(ISNA('QA Results'!D2:CY2),ISERROR(LOOKUP(2,1/SEARCH("Pass",'QA
Results'!D2:CY2),'QA Results'!$D$2:$CY$2))),"",LOOKUP(2,1/SEARCH("Pass",'QA
Results'!D2:CY2),'QA Results'!$D$2:$CY$2))

Any assistance would be appreciated.