complex search for words within a cell

**dangey** · 03-23-2010, 07:56 AM

i want to split a cell whilst also searching for a specific word.

have a list of manufacturers and its code in the same cell eg.

Company A.....A1234
Company B....B5678
etc

What i want to do is search for a specific company and then return the two values in two columns.

eg.

Manufacturer Code
Company A A1234

Struggling!

**NBVC** · 03-23-2010, 07:58 AM

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

**dangey** · 03-23-2010, 08:10 AM

ok heres a dummy workbook

what i want in this is to find "Ryan Air" and display its name in cell A11, the code in B11 and price in C11.

the code is always 5 numbers preceded by a letter and is always at the end of the cell.

**NBVC** · 03-23-2010, 08:21 AM

It seems you have regular periods in there and a special Char(133) period... is that really so?

If so,

in A11: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A3,CHAR(133),""),".",""),B11,"")

in B11: =RIGHT(A3,6)

in C11: =B3

all copied down

**martindwilson** · 03-23-2010, 08:24 AM

for code
=RIGHT(A3,6)
and assuming you always have some ...between (i must admit that's a strange 3 dot character)

=REPLACE(A3,FIND("…",A3),255,"")

**dangey** · 03-23-2010, 08:37 AM

there is not set amount of periods in there, which is quite annoying

is there any way i can search for the specific company or is it too complicated?

**NBVC** · 03-23-2010, 08:40 AM

My formula does not require a set amount of periods.. it replaces all of them whether regular "keyboard" period or special character (133) period... it replaces all of them.

When you say search for specific company.. what do you mean?

You want to manually enter a company name and then find the appropriate code and price?

**dangey** · 03-23-2010, 08:51 AM

it doesnt matter, think it will be simpler and less hassle to stick with what you've given me.

**NBVC** · 03-23-2010, 09:10 AM

Both are doable...

If you want to enter a company in A11, like Ryan Air... then in B11 use:

=RIGHT(VLOOKUP(A11&"*",$A$3:$B$6,1,0),6)

and in C11 use: =RIGHT(VLOOKUP(A11&"*",$A$3:$B$6,1,0),6)

formulas can be copied down to get more as you input into column A the companies you want to extract data for...