How to extract multiple instances of a keyword from a cell?

**wussupbuddy** · 08-14-2007, 03:12 AM

I wanna extract anyt text in a call that starts with LQ1 to a length of 10 characters to an empty cell, coma delimited. Is this possible?

**robert111** · 08-14-2007, 03:53 AM

using search you can find the location (in characters in from the start) of the text LQ1

eg text is applesLQ1pearsplumsoranges

the search returns 7

LEN(cellref) returns 26

MID(cellref,7,10) returns LQ1pearspl

**wussupbuddy** · 08-14-2007, 03:58 AM

Thanks rob, but this does not handle multiple instances of LQ1 in the text. So if I have text like applesLQ1pearsplumsoranges peacheslq1iloveanddig'em, I would like to get'em as LQ1pearspl, lq1ilovean

**oldchippy** · 08-14-2007, 04:02 AM

Not exactly sure what you want, but does this help, with your text in A1, put this in B1

=IF(LEFT(A1,3)="LQ1",LEFT(A1,3),"")

and this in C1

=IF(IF(LEFT(A1,3)="LQ1",LEFT(A1,3),"")="LQ1",MID(A1,4,7),"")

**robert111** · 08-14-2007, 04:08 AM

applesLQ1pearsplumsorangespeacheslq1iloveanddig'em

search to find the first example of LQ1 (at character 7)

in a temporary helper cell insert the formula

=mid(textcellref,search(textcellref,"LQ1")+10,200)

in here you will see umsorangespeacheslq1iloveanddig'em

now do the search on this cell

you can have as many tries as you like, you will get error messages where only one instance of LQ1, but you can trap these out using ISERROR

**wussupbuddy** · 08-14-2007, 04:16 AM

A better example

FRN EMTN 08/2007 CAD10000'B1339' 4.472 08/20/2007
Issuer: Abbey National Treasury Services plc (LSE:57AY) (IQT32033759)
08/20/2007

FLTG RATE NTS 8/2007 JPY100000000 4.333 08/21/2007
Issuer: Abbey National Treasury Services plc (LSE:57AY) (IQT31942187)
08/21/2007

FR GTD EMTN 22/08/2007 GBP100000 'B1340' 5.758 08/22/2007
Issuer: Abbey National Treasury Services plc (LSE:57AY) (IQT32037171)
08/22/2007

I need to put in the formula in a single cell to get the coma delimited list of the IQT id's

**oldchippy** · 08-14-2007, 04:27 AM

Try this,

Please Login or Register  to view this content.

**wussupbuddy** · 08-14-2007, 04:34 AM

Nope. Only gave me the first instance. - IQT32033759

**oldchippy** · 08-14-2007, 04:38 AM

I'm assumming that your sample is not in one cell here?? If it isn't, then auto-fill the formula down the column. Here's a link on auto-fill

http://mistupid.com/viewlets/excel/xlautofill.htm

**wussupbuddy** · 08-14-2007, 04:40 AM

As I mentioned in my original quote, it is in a single cell. Otherwise it would've been quite easy using find and mid.

**oldchippy** · 08-14-2007, 05:01 AM

Does this help?

**wussupbuddy** · 08-14-2007, 05:07 AM

Thanks, it does. But the problem is the number of instance of IQT is not fixed and I have like 1000 rows of this data. The reason I'm pressing for a single formula is that I had seen it on the web and now I can't find it.