Re: Extract specific numeric portion of text string

The ISERROR syntax would be like this

=IF(ISERROR(LOOKUP(10^6,RIGHT(LEFT(A5,FIND("k ",A5)-1),{1,2,3,4,5,6})+0)),"",LOOKUP(10^6,RIGHT(LEFT(A5,FIND("k ",A5)-1),{1,2,3,4,5,6})+0))

or you could use this shorter version to return a zero rather than an error

=LOOKUP(10^6,CHOOSE({1,2},0,LOOKUP(10^6,RIGHT(LEFT(A5,FIND("k ",A5)-1),{1,2,3,4,5,6})+0)))

Let me explain how the formula works.......

I used FIND to find the first position of "k " followed by a space and then left function then returns all the text up to the "k " but not including it, so where you have this text in A5

[COLOR="red"]05 of 10 2.05L BDGO 3Jun10 1000 Slow 2Y MDN-SW 15[/COLOR]k D Yendall (3) 57 ( SW) Btg:$8 $9 $11; 58.89 1-Ello Ello (B J Melham, 5) 57; 2-Legion's Belle (B Park, 4) 55; 3-Booklet (G Boss, 55; 9th rails. Off fence t. Angled clear top strt & hit line hard making good ground to line.

this part of the formula

LEFT(A5,FIND("k ",A5)-1)

returns just the part I marked in red

Now the clever part........

RIGHT function is used to give an array consisting of the rightmost 1,2,3,4,5 and 6 characters, so from this part

[COLOR="red"]05 of 10 2.05L BDGO 3Jun10 1000 Slow 2Y MDN-SW 15[/COLOR]

the rightmost 6 characters are "-SW 15" so this part

RIGHT(LEFT(A5,FIND("k ",A5)-1),{1,2,3,4,5,6})

returns this array

{"5","15"," 15","W 15","SW 15","-SW 15"}

Now I add 0 [+0].......and when you add a number to text you get an error so that array turns into this array

{5,15,15,#VALUE!,#VALUE!,#VALUE!}

Now I use LOOKUP on that array, i.e.

LOOKUP(10^6,{5,15,15,#VALUE!,#VALUE!,#VALUE!})

10^6 is simply 10 to the power of 6 = 1000000

When you lookup a large value like that in an array containing only smaller values it finds the last value, in this case 15 which is the value you want.......so {1,2,3,4,5,6} accommodates a maximum of a six digit number (or 5 digits with a decimal point)....which I assume is sufficient for your requirements here

regards, daddylonglegs