Check for Word in One Cell and process another cell

[Somniloquist]

Hi All Excellers out there...

Need a huge help...

I've some files where the data is like this....

Column Result
---------------------------------------
Wireless not working WLAN
Faulty HDD <wbr> HDD
Prob with Optical Drive ODD
and so on...

The Result is the column that are empty and i need to fill them
The Column is the column that have some sentences already

To Explain, The Column field will have generic sentences with symbols and so on.. Just like a normal sentence.
The Result column needs to have a word depending on what is in the sentence...
Hope that makes sense...
This thing works for one...
=IF(ISNUMBER(SEARCH("Wireless"<wbr>,E343)),"WLAN","")
What this did for me was search for the word wireless in cell E343 and returned value WLAN for me in my required cell.

Is there any way to do this for multiple values... Like build a small reference column and this would replace it from there just like how vlookup would work..
Please help me out on this. Please ask away anything that u need to know.

PS. I could have sent the data file. But its sensitive data with my company which im not allowed to.

 

[Hamburgler]

Not sure if I am on the right track. It sounds like you want to search each cell value in a particular range for a number of possible substrings, such as "wireless", and then if found return a corresponding value from some other lookup range. If this is the case then it sounds much easier to do with VBA/A macro than in a formula.

But I think that a long painful formula could do it. If the list of values being searched for is not too long you could do something like:

= IF(ISNUMBER(SEARCH("Wireless"<WBR>,E343)),"WLAN",
IF(ISNUMBER(SEARCH("NextValue"<WBR>,E343)),"ReturnThis",
""))

 

[Michael M]

Hi and welcome to the Board...are you awake now or posting in your sleep...LOL !!
I'd consider making a bit of a library using a VLOOKUP table OR using maybe the InStr function in a macro.
Either way I think your going to be finding the keyword AND the required word manually to create your table....which kinda defeats the purpose !!

As Hamburgler stated it depends on how many words and keywords you have !!

 

[Hamburgler]

Here is a custom function that may get you started. If you press ALT+F11 it will open the Excel VB Editor. You can then right click on any part of your workbook in the project window on the left side and select "Insert Module". You can then paste the code into the new empty module and you should be able to use it like a normal worksheet function. You can add additional "elseif" lines as needed. I am running something in Excel at the moment so I cant test it. Yell out if you have any problems.

Function FAULTTYPE(rng as range) as string
    dim x as string
    x=lcase(rng.value)
    if instr(x,"wireless") then
        FAULTTYPE = "WLAN"
    elseif instr(x,"hard drive") then
        FAULTTYPE = "HDD"
    elseif instr(x,"optical") then
        FAULTTYPE = "OPT"
    else
        FAULTTYPE = "UNKNOWN"
    endif
end function

 

[Somniloquist]

TY Both So much for responding...
PS> Not Asleep. Never Expected for such a fast reply...

I am at home atm so i do not have access to the file. I will surely run a check and provide you with more updates.

In order to explain a bit more - this is what i gotta do...
EG:
Column E has sentences with my required keywords like x,y,z, n so on...
Now i wanna make a table so that
Found Replace With
x xx
y yy
z zz

I need Column F to have the the replaced values only.

Its more like - I read the string , get that keyword and paste it in Column F

Thank You...