Extracting numbers that appear after specific text

Hi and welcome to ExcelForo!

Originally Posted by RK94

1. (To be extracted in one column) The voting numbers taking the form "X-Y" that appear immediately after the first time the phrase "House House Floor" appears
...

not always separated by semicolon etc

If not ";", what others?
The following formula considers: space , . ;

Please Login or Register  to view this content.

1. A non=working formula rarely helps.

2. Several expected results, manually calculated, IN your sample sheet (not in the body of the post) does help considerably!

3. I have allowed for UP TO 4 pairs of votes in each text box. If you need more, then the bits in red should be adjusted to be 2x the maximum number needed. So, if you need 6 pairs of votes... change to 12.

=IFERROR(FILTERXML("<A><B>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A2,"-"," "),";"," ")," ","</B><B>")&"</B></A>","//B[preceding::*["&1+MOD(SEQUENCE(,8)-1,2)&"]='P']["&INT(SEQUENCE(,8,1,1/2))&"]"),"")

copied down.

On re-reading:

=IFERROR(FILTERXML("<A><B>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A2," P ","-"),";","-"),"-","</B><B>")&"</B></A>","//B[contains(preceding::*["&1+MOD((COLUMNS($B2:B2)-1),2)&"], 'House House')]["&1+INT((COLUMNS($B2:B2)-1)/2)&"]"),"")

copied across and down and also this one, also copied across and down.

=IFERROR(FILTERXML("<A><B>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A2," P ","-"),";","-"),"-","</B><B>")&"</B></A>","//B[contains(preceding::*["&1+MOD((COLUMNS($F2:F2)-1),2)&"], 'Senate Senate')]["&1+INT((COLUMNS($F2:F2)-1)/2)&"]"),"")

If incorrect, DUMP the non-working formulae in your sample sheet, addd representative examples and expected results.

*** Update - removed attachment to avoid unnecessarily sharing info and posted what appears to be a solution below. Thanks again Glenn***

Hi Glenn, wow - thanks so much for this.

It looks the formula is working for a bunch of text entries but about half are still returning a blank erroneously (/an error without the iferror wraparound).

I've followed your advice and included in the updated attachment ("Extract 2") a small subset of text entries (dates and statistics changed), and expected results (together with your formula in the middle for the House House Floor portion).

Really appreciate any guidance you can share!

Best,
RK94

Administrative Note:

Members will tailor the solutions they offer to the version (NOT release number) of Office (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.

Originally Posted by RK94

Hey Dante Amor!

I'm not sure I understand your approach - are you able to clarify how to make it return the result "65-30" in the example above?

The text to look for is in cell B1, that way you only write it once. The formula is simplified to this:

=IFERROR(LEFT(SUBSTITUTE(REPLACE($A2,1,SEARCH(B$1,$A2)+LEN(B$1),""),";",REPT(" ",99)),99), "No information")

By the way, this way there is no case sensitivity.