Data search inside a cell

[guna_sekar87]

Problem Description: For each data in colA, i want to search a specific criteria in ColB and put the search result in ColC.

ColA ColB ColC


Row1 Data1 X1/a,b X2/a X3/c,d X4/b

Row2 Data2 X1/b X2/a,b X4/d

Row3 Data3 X1/b,c X2/c,b X3/c

Row4 Data4 X1 X4


Example Case 1 :

I want to search X2/b in all coloumnB for each row

So, For Data1, I want a new ColoumnC as “absent”

For Data2, I want a new ColoumnC as “present”

For Data3, I want a new ColoumnC as “present”

For Data4, I want a new ColoumnC as “absent”

Example Case 2 :

I want to search X4/c in all coloumnB for each row

So, For Data1, I want a new ColoumnC as “absent”

For Data2, I want a new ColoumnC as “absent”

For Data3, I want a new ColoumnC as “absent”

For Data4, I want a new ColoumnC as “absent”


Kindly do the needfull for this problem

 

[nazmul_muneer]

Dear Guna_sekar87

Please provide this data in a excel file and upload it.


Regards


Muneer

 

[kaushik03]

Hi Guna,


I am little confused by looking at the data set you have posted.

[pre]

ColA       ColB         ColC
X1/a	   b X2/a X3/c	d X4/b
X1/b X2/a  b X4/d
X1/b	c  X2/c	        bX3/c

[/pre]

According to your post, you are expecting "present" (as the result) when you search "x2/b" in Col B row2.But in Col B there is no x2/b.


However, you could use the below formula for searching:


=IFERROR(IF(SEARCH("x2/b",B2)>0,"present"),"absent") and drag it down.


If there would have been "x2/b" at row 2 of Col B , the formula would return you "present".


You can do the same for "x4/c".


Please let me know if I have misunderstood the data layout. Or please consider uploading a sample file as suggested by Muneer in the previous post.


Regards,

Kaushik

 

[guna_sekar87]

Dear team,


Kindly use this attachment:

As a info,:

","operator used to include "the list of characters" under a "X"

for example : X2/a,b means both a nd b are under X2(ie.X2/a,b = X2/a X2/b)


http://www.fileconvoy.com/dfl.php?id=g6dac57b184610d22999232520f67553951a627f4e


Edit by Hui:

Try this link as the above opens as a PDF

https://www.dropbox.com/s/p7cp5x3ijifxyb8/guna_sekar87_data.xlsx

 

[Faseeh]

Hi guna_sekar87,


Can you please try this formula:

=IF(ISERROR(FIND("b",LEFT(RIGHT(B1,LEN($B1)+1-FIND("X2",$B1)),FIND(" ",RIGHT(B1,LEN($B1)+1-FIND("X2",$B1))))))=FALSE,"Present","Absent")

Faseeh

 

[Debraj]

Hi guna_sekar87,


Please download the below file..

Play with it.. and check if its fine...


Attach file is full of Helper Columns.. if your exact data are always.. in above pattern..

then we can remove the helper columns also..


https://dl.dropbox.com/u/78831150/Excel/guna_sekar87_data%20%28Seach%20Text%20Pattern%29.xlsx


* for separation of Text and convert it into a perfect pattern.. I have used...

=SUBSTITUTE(TRIM(MID(SUBSTITUTE($B1," ",REPT(" ",99)),((COLUMN(A1)-1)*99)+1,99)),",",LEFT(TRIM(MID(SUBSTITUTE($B1," ",REPT(" ",99)),((COLUMN(A1)-1)*99)+1,99)),3))


which will convert "X1/a,b X2/a X3/c,d X4/b,a" to

X1/aX1/b	X2/a	X3/cX3/d	X4/bX4/a

and then you can search your desired string..


and if this is not the pattern.. then I think we should go for some Coding...


Regards,

Deb

 

[guna_sekar87]

can u please provide me the link in fileconvey.com,because this web only i cab aable to access in my office

 

[Faseeh]

@guna_sekar87,


Have you tried formula in my last post?? Is it working correctly?? you can replace the values X2 and b in it.


Faseeh