Extract number from list

Values in my worksheet:

A1 : 1,3,7,8,12
B1 : 3

I'm looking for a formula that will extract the next # from the sequence in A1 after the value in B1.
IOW I want C1 to equal 7 in this instance.

A1 will contain a maximum of 6 #s separated by ",". I have no problem with it taking more than 1 cell to calculate intermediate values.

Would the below work for you ?

=LOOKUP(9.99E+307,--MID(REPLACE(A1,1,FIND(B1&",",A1&",")+LEN(B1&",")-1,""),1,ROW($1:$15)))

You would get #N/A if the number does not exist or is last numeric in the string

(if the numbers are within known boundaries then the above could be much simplified I'm sure)

EDIT:

to account for comma delimiter... eg 7,123,345 etc...

=LOOKUP(9.99E+307,--MID(REPLACE(SUBSTITUTE(A1,",","@"),1,FIND(B1&",",A1&",")+LEN(B1&",")-1,""),1,ROW(1:15)))

The prior suggestion would still work but in terms of dispensing with some of the complexity in lieu of the above perhaps then:

=--SUBSTITUTE(LEFT(REPLACE(A1,1,FIND(B1&",",A1&",")+LEN(B1),""),2),",","")

or

=--SUBSTITUTE(MID(A1,FIND(B1&",",A1&",")+LEN(B1&","),2),",","")

#VALUE! would result if either B1 not found or B1 was last numeric - not known what you want to do in such instances.

Originally Posted by foxguy

Why is there a reference to A2...

Sorry, my bad - A2 should read B1 in all suggestions - I will modify all prior posts accordingly.

Originally Posted by foxguy

... and Row() ?

Use of ROW in the first suggestion (before we knew pre determined numeric range) is to generate 15 strings of increasing length starting from a given character - each sting will be coerced to number - LOOKUP will return the last value that was coerced successfully)

Originally Posted by foxguy

I don't remember what the "--" signifies

-- is double unary operator and is used here to coerce resulting text string to number (eg --"7" -> 7 .. you could use VALUE or 1* or 0+ etc.. if preferred)

Originally Posted by foxguy

Your formula doesn't work. In my example, I want the # after 3 (7), your formula returns 3.

Using your sample string and criteria all four suggestions would return 7.

Please post a sample.

Regards the below which I missed:

Originally Posted by foxguy

If B is the last # in the sequence, then I want the 1st # in the sequence.

...using all four prior examples any one of:

=LOOKUP(9.99E+307,--MID(REPLACE(A1&","&LEFT(A1,2)&",",1,FIND(B1&",",A1&",")+LEN(B1&",")-1,""),1,ROW($1:$15)))

=LOOKUP(9.99E+307,--MID(REPLACE(SUBSTITUTE(A1&","&LEFT(A1,2)&",",",","@"),1,FIND(B1&",",A1&",")+LEN(B1&",")-1,""),1,ROW(1:15)))

=--SUBSTITUTE(LEFT(REPLACE(A1&","&LEFT(A1,2)&",",1,FIND(B1&",",A1&",")+LEN(B1),""),2),",","")

=--SUBSTITUTE(MID(A1&","&LEFT(A1,2)&",",FIND(B1&",",A1&",")+LEN(B1&","),2),",","")

though I'd say the LOOKUPs are overkill (the 2nd in particular given the max 2 digit and US config)