Find and return 2nd instance of value in column

[srice33]

I have a specific text string that I am trying to return the value for from within a column of data. I want to return the date portion of the text string for the second occurrence of the text string.

For example, the text string begins with "As of" and ends with a date. I.e. "As of 05/06/2024".

There are numerous text strings like this within the column (i.e. first instance might be "As of 07/01/2024" in row 1, second instance "As of "09/15/2024" in a later row), and I want to return the second instance when the string begins with "As of".

The return value needs to be just the date portion of the string, i.e. 09/15/2024.

 

[ExcelToDAX]

Assuming that first column is in column A, how about:

=TEXTAFTER(INDEX(FILTER(A:A,LEFT(A:A,5)="As of"),2),"As of ")

 

[dreid1011]

Another option, but will find "As of" anywhere in the string, not just the beginning:

Book1
	A	B
1
2	As of 7/1/2024	9/15/2024
3	sample text
4	sample text
5	sample text
6	sample text
7	sample text
8	sample text
9	sample text
10	sample text
11	sample text
12	sample text
13	sample text
14	As of 9/15/2024
15	sample text
16	sample text
17	sample text
18	sample text
19	sample text
20	sample text
21	sample text
22	sample text
23	sample text
24	sample text
25	sample text
Sheet1

Cell Formulas
Range	Formula
B2	B2	=TEXTAFTER(CHOOSEROWS(FILTER(A2:A25,ISNUMBER(SEARCH("As of ",A2:A25))),2),"As of ")

 

[srice33]

Much appreciated - is there any alternative to the text after function? I am having to use sheets and it is not supported.

 

[dreid1011]

Much appreciated - is there any alternative to the text after function? I am having to use sheets and it is not supported.

You can use RIGHT and LEN like this:

=RIGHT(INDEX(FILTER(A:A,LEFT(A:A,5)="As of"),2),LEN(INDEX(FILTER(A:A,LEFT(A:A,5)="As of"),2))-6)

=RIGHT(CHOOSEROWS(FILTER(A2:A25,ISNUMBER(SEARCH("As of ",A2:A25))),2),LEN(CHOOSEROWS(FILTER(A2:A25,ISNUMBER(SEARCH("As of ",A2:A25))),2))-6)

 

[srice33]

You can use RIGHT and LEN like this:

=RIGHT(INDEX(FILTER(A:A,LEFT(A:A,5)="As of"),2),LEN(INDEX(FILTER(A:A,LEFT(A:A,5)="As of"),2))-6)

=RIGHT(CHOOSEROWS(FILTER(A2:A25,ISNUMBER(SEARCH("As of ",A2:A25))),2),LEN(CHOOSEROWS(FILTER(A2:A25,ISNUMBER(SEARCH("As of ",A2:A25))),2))-6)

Got it to work, thank you!!

 

[dreid1011]

You're welcome, and happy to help.

 

[Joe4]

I am having to use sheets and it is not supported.

As you can see, while there are many similarities between Excel and Google Sheets, they are NOT same. As such, you should not post Google Sheets questions in the "Excel Questions" forum. You should post them to the "General Discussion & Other Applications" forum (note the description on it):

Also, we recommend that you either mention in your title and/or original question that you are using Google Sheets, since this forum is for all other programs (not just Google Sheets).
Then it will be clear to users exactly which program you are working on.

I have moved this thread to the appropriate forum.