Hi
I need to edit my excel worksheet in such a way as shown:
A1 data is: x/Makhushi?fref=
A2 data is: w/m/x/jeorge?fref=
D1 data is: David is going home
D2 data is: jeorge is fine
D3 data is: is Makhushi a good boy
I want B columns as by matching subtext in column A and D
B1: Makhushi a good boy
B2: jeorge is fine
How do I achieve this if I have data up to 500 ROWS ?
PS: TEXT CAN BE MISSING IN ANY CELL. matching text must be case insensitive.
Please help.
Here, try this UDF
use function as:
Please Login or Register to view this content.
Formula:
Please Login or Register to view this content.
Thank you for your anawer.
I changed A1 data "x/Makhushi?fref" to "x/Makhushi.sim.1?fref". And I could not get anything. I need the code to work that way as well sir.
How to declare case sensitivity ?
It's not about case sensivity.
It's about your string.
"x/Makhushi.sim.1?fref" is different than "x/Makhushi?fref" and you didn't explain do you want to compare for example:
Makhushi
or
Makhushi.sim.1
my mistake apologies.
I want if Makhushi is found in any way then it takes "is Makhushi a good boy" from D.
For example:
Makhushi.iso
iso.Makhushi/tree.bell
tree/bell/makushi/train/tree/bell/makushi/train/
x/Makhushi.sim.1?fref
Hope I am clear now.
ok now.
Two things:
1. Based on upper example separators between words are / . and ?
Can there be any more separators like =
2. If you separate words such way then it will return first matchin criteria.
For example, if in D column you have:
Then when you separate text: tree/bell/makushi/train/tree/bell/makushi/train/Please Login or Register to view this content.
Into words and try to match D column it will return: John climb on the tree
because word tree is first of checking words and there is no any reason why check makushi and not check tree so I must check them all.
If this is something that you don't want to then you can define one more column with just matching words.
For example like this:
That will match criteria word and return text next to it.Please Login or Register to view this content.
So tree/bell/makushi/train/tree/bell/makushi/train/
Will return : is Makhushi a good boy
Because tree is not matching word.
Is this make sense?
Last edited by zbor; 08-17-2015 at 11:32 AM.
1. separators can be anything. (even makushi can be found as amakushisionara, bbababbamakushisi)
2. Yes its a great idea. So can I get the result in multiple columns like
A1: tree/bell/makushi/train/tree/bell/makushi/train/, E1: is Makhushi a good boy, F1: train is on time
PS: in C1 and D1 I will put the word and sentences respectively.
This is exactly what I need. Thanks in advance.
Now you need to define 3 ranges by formula:
- Range to test
- Range with crucial word
- Range with result
Formula:Please Login or Register to view this content.
Here is a code:
Please Login or Register to view this content.
Note: Ranges where you define data can be bigger than data but must be same size
Formula:Please Login or Register to view this content.
Thank you.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks