Find a string in a cell

K

Kiran1683

New Member
Joined
Apr 13, 2013
Messages
7
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]server1 Name Along with somedata
[/TD]
[TD][/TD]
[TD]server1
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Name along with otherdata server4
[/TD]
[TD][/TD]
[TD]server2
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Somthing server5 other values
[/TD]
[TD][/TD]
[TD]server3
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD][/TD]
[TD][/TD]
[TD]server4
[/TD]
[/TR]
</tbody>[/TABLE]
Hello Team,
Here I have a typical question, where in a cell there is a sentance with many strings. i wan to search that cell, wetaher the cell contains any string compared to an array or column of strings. Ex: In A1 , a sting named server1 is there (irrespective of postion). I need to compare the cell with entire C:C and print the matched sting in B1 (i.e server1) and similar B2 (server4), B3 ("No Server").

Please help me.
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Sort by date Sort by votes
Kiran1683 said:
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]server1 Name Along with somedata
[/TD]
[TD][/TD]
[TD]server1
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Name along with otherdata server4
[/TD]
[TD][/TD]
[TD]server2
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Somthing server5 other values
[/TD]
[TD][/TD]
[TD]server3
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD][/TD]
[TD][/TD]
[TD]server4
[/TD]
[/TR]
</tbody>[/TABLE]
Hello Team,
Here I have a typical question, where in a cell there is a sentance with many strings. i wan to search that cell, wetaher the cell contains any string compared to an array or column of strings. Ex: In A1 , a sting named server1 is there (irrespective of postion). I need to compare the cell with entire C:C and print the matched sting in B1 (i.e server1) and similar B2 (server4), B3 ("No Server").

Please help me.
Click to expand...

B1, just enter and copy down:

=LOOKUP(9.99999999999999E+307,SEARCH($C$1:$C$100,A1),$C$1:$C$100)
 
Upvote 0
Welcome to the MrExcel board!

I think you'd need to restrict the column C reference to the cells actually containing values (ie avoiding any blank cells at the end)
And, assuming Excel 2007 or later, add the IFERROR to deal with cases where there is no match

Formula in B1 copied down.

Excel Workbook
ABC
1server1 Name Along with somedataserver1server1
2Name along with otherdata server4server4server2
3Somthing server5 other valuesNo Serverserver3
4What about Server3 today?server3server4
5Please fix Server4server4
6This has nothingNo Server
7Note what happens if 2 strings Server1 and Server2server2
8
Find String
 
Upvote 0
Hello Mr.Aladin and Mr. Peter,

Thanks for replying. You guys are genius. Formulae works awesome. However, there is a samll problem. It searchs for first match and stops, I have the list in column C, where server1, server2,server3.... and server11,server12 and so on. But the given formula search for server1 and gives the result for which A1 contains server12 as server1, instead of server12. And FYA,the search string in A1, will be having a space between the words (like test string server12 some more text). I need to search whole string match like server12, but it shouldn't stop by server1 (as refered in C column). Hope u got it, wat I'm asking for. Can we make this serach case sensetive? Please help me with your expertise. Thank u :)
 
Upvote 0
Kiran1683 said:
Hello Mr.Aladin and Mr. Peter,

Thanks for replying. You guys are genius. Formulae works awesome. However, there is a samll problem. It searchs for first match and stops, I have the list in column C, where server1, server2,server3.... and server11,server12 and so on. But the given formula search for server1 and gives the result for which A1 contains server12 as server1, instead of server12. And FYA,the search string in A1, will be having a space between the words (like test string server12 some more text). I need to search whole string match like server12, but it shouldn't stop by server1 (as refered in C column). Hope u got it, wat I'm asking for. Can we make this serach case sensetive? Please help me with your expertise. Thank u :)
Click to expand...

Requires a small tweak to distinguish between server12 and server1, using space char as delimiter:

=LOOKUP(9.99999999999999E+307,SEARCH(" "&$C$1:$C$100&" "," "&A1&" "),$C$1:$C$100)

Are you wanting to report multiple occurrences in a target string if such is case?
 
Upvote 0
Hello Mr. Aladin,

Million Thanks to u... :) Yes.. There might be multiple occurrences in the target string. You are simply awesome dude.
 
Upvote 0
Kiran1683 said:
Hello Mr. Aladin,

Million Thanks to u... :) Yes.. There might be multiple occurrences in the target string. You are simply awesome dude.
Click to expand...

You are welcome.

If you want multiple results... Using the sample Peter posted:

[TABLE="width: 548"]
<colgroup><col style="width: 319pt; mso-width-source: userset; mso-width-alt: 15132;" width="426"> <col style="width: 20pt; mso-width-source: userset; mso-width-alt: 967;" width="27"> <col style="width: 52pt; mso-width-source: userset; mso-width-alt: 2446;" width="69"> <col style="width: 48pt;" width="64"> <col style="width: 54pt; mso-width-source: userset; mso-width-alt: 2560;" width="72"> <col style="width: 55pt; mso-width-source: userset; mso-width-alt: 2588;" width="73"> <tbody>[TR]
[TD="class: xl65, width: 426, bgcolor: white"]server1 Name Along with somedata[/TD]
[TD="class: xl65, width: 27, bgcolor: white"] [/TD]
[TD="class: xl65, width: 69, bgcolor: white"]server1[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl66, width: 72, bgcolor: transparent"]server1[/TD]
[TD="class: xl66, width: 73, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, width: 426, bgcolor: white"]Name along with otherdata server4[/TD]
[TD="class: xl65, width: 27, bgcolor: white"] [/TD]
[TD="class: xl65, width: 69, bgcolor: white"]server2[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"]server4[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, width: 426, bgcolor: white"]Somthing server5 other values[/TD]
[TD="class: xl65, width: 27, bgcolor: white"] [/TD]
[TD="class: xl65, width: 69, bgcolor: white"]server3[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, width: 426, bgcolor: white"]What about Server3 today?[/TD]
[TD="class: xl65, width: 27, bgcolor: white"] [/TD]
[TD="class: xl65, width: 69, bgcolor: white"]server4[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"]server3[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, width: 426, bgcolor: white"]Please fix Server4[/TD]
[TD="class: xl65, width: 27, bgcolor: white"] [/TD]
[TD="class: xl65, width: 69, bgcolor: white"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"]server4[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, width: 426, bgcolor: white"]This has nothing[/TD]
[TD="class: xl65, width: 27, bgcolor: white"] [/TD]
[TD="class: xl65, width: 69, bgcolor: white"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, width: 426, bgcolor: white"]Note what happens if 2 strings Server1 and Server2[/TD]
[TD="class: xl65, width: 27, bgcolor: white"] [/TD]
[TD="class: xl65, width: 69, bgcolor: white"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"]server1[/TD]
[TD="class: xl66, bgcolor: transparent"]server2[/TD]
[/TR]
</tbody>[/TABLE]

E1, control+shift+enter (not just enter), copy across, and down:
Rich (BB code): 
=IFERROR(INDEX($C$1:$C$4,
  SMALL(IF(ISNUMBER(SEARCH(" "&$C$1:$C$4&" "," "&$A1&" ")),
  ROW($C$1:$C$4)-ROW($C$1)+1),COLUMNS($E2:E2))),"")

We can also get the results into a single cell like "server1, server2", using a UDF, if so desired.
 
Upvote 0
Hello Mr.Aladin,

Thanks for giving one more formulae for future filtering. Super. Can u please explain why to use 9.999999...E+307? Is this value equal to 1.? :biggrin:
 
Upvote 0
Kiran1683 said:
Hello Mr.Aladin,

Thanks for giving one more formulae for future filtering. Super.
Click to expand...

You are welcome.

Can u please explain why to use 9.999999...E+307? Is this value equal to 1.? :biggrin:
Click to expand...

It's a constant of Excel and is used as lookup up value in LOOKUP and kindred functions in order to fetch or locate the last numeric value in a reference of interest. Here are some links on the subject:

http://www.mrexcel.com/forum/excel-questions/102091-9-9999999-a.html
http://www.mrexcel.com/forum/excel-questions/310278-vlookup-multiple-matches-match-returned.html (post #7)

Hope this helps.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Find an entire string in a cell
Replies
1
Views
235
Fluff
Fluff
jag108
  • Question Question
Create dynamic table in apointment body
Replies
5
Views
468
jag108
jag108
M
  • Question Question
Textjoin in an array using a search string
Replies
4
Views
8K
mackensteff
M
I
  • Question Question
Need to copy specific string of text from column into another cell
Replies
5
Views
989
shijith
shijith
A
  • Question Question
VBA - Deleting a chain of characters specified in an another cell
Replies
3
Views
1K
Alex38
A

Forum statistics

Threads
1,226,730
Messages
6,192,699
Members
453,747
Latest member
tylerhyatt04

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top