Extracting a certain string of text from a cell

Hi everyone,

Basically, I'm trying to extract a numbers from a cell containing text (for anyone interested, I'm basically trying to get the quantity bought/sold of a stock). For example, in cell A1, I could have the text string:

SOLD 565 MGNT.MM

or

BGHT 10,203,123 M.LN

Basically, it will start with the the 4-letter words 'SOLD' or 'BGHT' and then a number (of varying length) followed by a string of text of varying length (I think it's between 4 and 7 characters long)

Is there a way I can extract the number easily?

I was thinking of using the method shared here (http://www.mrexcel.com/forum/excel-q...xt-string.html) but I don't fully understand the formula. Basically, the number I need (in the above examples, it would be 565 or 10,203,123) always comes after a space.
Thanks!

=lookup(10^10,mid(substitute(a1,",",""),5,row($1:$10))+0)

With
A1: a string beginning with 4-letters, a space, then numeric text...followed by a space and other text
SOLD 565 MGNT.MM


this formula returns the numeric string

Please Login or Register  to view this content.

In that example, the formula returns: 565

Is that something you can work with?

=MID(LEFT(A1,FIND(" ",A1,6)-1),6,100)*IF(COUNTIF(A1,"*sold*"),-1,1)

With
A1: text beginning with either SOLD or BGHT (or blank)

This formula returns the numeric part after the prefix, converting to negative if the prefix is SOLD. If A1 is blank, the formula returns a text-blank.

Please Login or Register  to view this content.

Is that something you can work with?