formula to extract substring from text string

[miconian]

I have a series of cells that contain text which looks like this:

blahblah blah blah blah BLAHBLAH line 23blahblahblah

The "23" is the relevant part. It's always a number, it could be from one to three digits long, and it always comes just to the right of "line" followed by a space.

I basically need to isolate that number and put it in another cell.

Seems to me the answer involves the use of FIND and MID. But what trips me up is the fact that the string I'm looking for is of variable length.

Any thoughts? Thanks so much for your help!

 

[Special-K99]

http://office.microsoft.com/en-us/e...rs-from-alphanumeric-strings-HA001154901.aspx

 

[Firefly2012]

Hi

One option:

Excel 2010
	A	B
4	blahblah blah blah blah BLAHBLAH line 23blahblahblah	23
5	some rubbish line 999 and some more junk	999
Sheet1

Cell Formulas
Range	Formula
B4	=LOOKUP(1000,--MID(A4,SEARCH("line ",A4)+5,{1,2,3}))

 

[TMShucks]

One way, maybe:

=IF(RIGHT(MID(A1,FIND("line ",A1)+5,3),1)>="a",--MID(A1,FIND("line ",A1)+5,2),--MID(A1,FIND("line ",A1)+5,3))

 

[T. Valko]

I have a series of cells that contain text which looks like this:

blahblah blah blah blah BLAHBLAH line 23blahblahblah

The "23" is the relevant part. It's always a number, it could be from one to three digits long, and it always comes just to the right of "line" followed by a space.

I basically need to isolate that number and put it in another cell.

Seems to me the answer involves the use of FIND and MID. But what trips me up is the fact that the string I'm looking for is of variable length.

Any thoughts? Thanks so much for your help!

Is it possible that the text might include more than 1 substring of "line "?

It's better to post real examples that will give us a better idea of what we need to look for.

 

[miconian]

Thanks, Firefly. Seems to work perfectly. Although some of the results are inexplicably underlined.

SpecialK, that might have worked too, although despite my 'blah' example, there are actually other numbers in the string I'm extracting from.

 

[Aladin Akyurek]

Try...

=LOOKUP(9.99999999999999E+307,--LEFT(REPLACE(A2,1,SEARCH("line ",A2)+4,""),ROW(INDIRECT("1:"&LEN(A2)))))

 

[miconian]

Thanks, Aladin, that works too.