Find words in a cell

[neilabcd]

Hi Experts,

i desperately need help with a formula here.
I have a huge data sheet with a column which has revelevt info in the below format -

**Unworkable code removed**

i need to extract the below words and then use a vlookup to get what info i need.

**Unworkable code removed**

Foe ex, the first fig, has a cell with PUB03 in it, i need to extract this above list from the first column.

How do i do this?

 

[neilabcd]

Find them and do what with them?
If you already have the big column of data and you already have the table in the second screen shot, what actual result(s) do you want, and where do you want it(them)?
I'm afraid I have no idea what I am trying to actually produce:

Also, is it possible that more than one of the "words" can appear in a single cell in the big column and if so does that affect what should happen?

Ok so the words wont be repeated.
i am looking to find the works in the first col of the 2nd Fig in the big list of data
after extracting that, i will do a vlookup to the 2nd pic, to get the names of those codes.
let me know if that helps

 

[Peter_SSs]

i am looking to find the works in the first col of the 2nd Fig in the big list of data
after extracting that, i will do a vlookup to the 2nd pic, to get the names of those codes.

Can we do it all at once? I think this is what you are aiming for.

Formula copied down.

Excel Workbook
	G	H	I	J	K	BL	BM
2	D&I01	e-Borders
3	D&I02	Defence Modernisation
4	HEA01	Electronic Patient Records
5	GOV01	Intelligent Transport
6	GOV02	Police Analytics
7	GOV03	Police Command & Control
8	GOV04	Declaration Management
9	SS01	FOD/Self service
10	PUB01	Application Modernisation				SMRTCITY-SMRTSSSS, ISAPG30-, GBSSECT-PUB03, zGrowth-ZSPCTY02	CURAM
11	PUB02	Fraud & Error				GBSSECT-PUB01, ISAPG30-	Application Modernisation
12	PUB03	CURAM				PROC-IGSPU, SMRTCITY-SMRTPSAF, ISAPG51-, GBSSECT-D&I02	Defence Modernisation
13						PROC-IGSPU, ISAPG51-, GBSSECT-D&I02	Defence Modernisation
14						PROC-IGSPU, ISAPG51-, GBSSECT-D&I02	Defence Modernisation
15						PROC-IGSPU, ISAPG51-, GBSSECT-D&I02	Defence Modernisation
Extract

 

[neilabcd]

Can we do it all at once? I think this is what you are aiming for.

Formula copied down.

Extract

*	G	H	I	J	K	BL	BM
2	D&I01	e-Borders	*	*	*	*	*
3	D&I02	Defence Modernisation	*	*	*	*	*
4	HEA01	Electronic Patient Records	*	*	*	*	*
5	GOV01	Intelligent Transport	*	*	*	*	*
6	GOV02	Police Analytics	*	*	*	*	*
7	GOV03	Police Command & Control	*	*	*	*	*
8	GOV04	Declaration Management	*	*	*	*	*
9	SS01	FOD/Self service	*	*	*	*	*
10	PUB01	Application Modernisation	*	*	*	SMRTCITY-SMRTSSSS, ISAPG30-, GBSSECT-PUB03, zGrowth-ZSPCTY02	CURAM
11	PUB02	Fraud & Error	*	*	*	GBSSECT-PUB01, ISAPG30-	Application Modernisation
12	PUB03	CURAM	*	*	*	PROC-IGSPU, SMRTCITY-SMRTPSAF, ISAPG51-, GBSSECT-D&I02	Defence Modernisation
13	*	*	*	*	*	PROC-IGSPU, ISAPG51-, GBSSECT-D&I02	Defence Modernisation
14	*	*	*	*	*	PROC-IGSPU, ISAPG51-, GBSSECT-D&I02	Defence Modernisation
15	*	*	*	*	*	PROC-IGSPU, ISAPG51-, GBSSECT-D&I02	Defence Modernisation

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:56px;"><col style="width:169px;"><col style="width:17px;"><col style="width:17px;"><col style="width:17px;"><col style="width:488px;"><col style="width:169px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula BM10 =LOOKUP(2,1/SEARCH($G$2:$G$12,BL10),$H$2:$H$12) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Sorry for being dumb here, i just copied the data like you have and used your formula, but i get all zero.
also the first table here (lookup) is in another sheet for me. i am unable to understand how you have used the lookup (2,1 part)
Help! Plz!

 

[neilabcd]

Sorry for being dumb here, i just copied the data like you have and used your formula, but i get all zero.
also the first table here (lookup) is in another sheet for me. i am unable to understand how you have used the lookup (2,1 part)
Help! Plz!

nah.. tried everythng, above formula doesnt work for me.. using excel 2010 btw

 

[Peter_SSs]

Sorry for being dumb here, i just copied the data like you have and used your formula, but i get all zero.
also the first table here (lookup) is in another sheet for me. i am unable to understand how you have used the lookup (2,1 part)
Help! Plz!

No need to apologise - the forum is here to help in situations like this.

Firstly, when you say you copied the data like I have, can you confirm that to test you have put it in the exact ranges I did, noting that I hid columns L:BK so my screen shot would fit in my post?

See if you can get it to work just with this exact layout & data first, then we'll worry about larger data and the fact that the lookup table is on another sheet.

What is the name of the sheet that the lookup table is on?

What is the name of the sheet the column BL data (and results) is on?

I am also using Excel 2010 and you can see it is working in my example, with your data copied from post #6, so we should be able to get it to work for you.

 

[neilabcd]

yoo hoo it works!!! thanks a million, u were right, i missed looking for the right column.. thanks again.

 

[Peter_SSs]

yoo hoo it works!!! thanks a million, u were right, i missed looking for the right column.. thanks again.

Great news!

And have you adapted for the lookup list being on another sheet too?

 

[AntoniaI]

I see that you words are comma separated so you can spred them in separate cels automatic. after that is easy to use vlookup.

To spread the in different cels follow my lead:

"data" tab->"text to columns"-> delimited and from here i thing you can manage!

 

[neilabcd]

@Peter, yes, i was albe to adapt, thanks again

 

[Peter_SSs]

I see that you words are comma separated ...

No they aren't. For example in cell BL11 in my example (BL1379 in the original sample), the cell value is "GBSSECT-PUB01, ISAPG30-" but the "word" being searched for is "PUB01" - not comma separated.