Using the =IF(ISNUMBER(SEARCH function to search multiple cells...

[nickwood1066]

Hi,

I' having trouble using the =IF(ISNUMBER(SEARCH formula to search multiple cells.

I can get it to work to search one cell (as below):

=IF(ISNUMBER(SEARCH("Same",G10)),"No Change",'Aug 09 Matrix'!F10)

(In this case the respone is No Change as Cell G10 contains "Same")

However cannot get it to work for several cells. I have pasted below and highlighted the function I would like it to perform:

=IF(ISNUMBER(SEARCH("Same",G10:R10)),"No Change",'Aug 09 Matrix'!F10)

Essentially the other cells in that row (G10:R10) all contain "Same", however for some reason it is identifying this as FALSE and putting in the data from the 'Aug 09 Matrix' sheet.

Any idea?

Any help much appreciated!

Best regards

Nick

 

[iazeemkhan]

Dear Sir,

Apology to restart the Thread. Sir, is it possile to get result in Cdolumn B with comma after each result example below.

VBA can also be do.

	A	B	C	D	E	F
1
2	John has working knowledge of Oracle and Excel	Excel,John,Oracle				DB
3						ESMDB
4	Excel					Excel
5	John					FESMDB
6	Oracle					Hardware
7						John
8						MDB
9						MySQL
10						Oracle
11						Rajesh
12						Raju
13						SMDB
14						Software
15						Toad

<tbody>
</tbody>

Sheet1​

Name the sorted list in F2:F15 List. Note that the sorting must be in ascending order.

In A4 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX(List,SMALL(IF(ISNUMBER(SEARCH(" "&List&" "," "&$A$2&" ")),ROW(List)-ROW(INDEX(List,1,1))+1),ROWS($1:1))),"")

 

[Aladin Akyurek]

Dear Sir,

Apology to restart the Thread. Sir, is it possile to get result in Cdolumn B with comma after each result example below.

VBA can also be do.

Either: Control+shift+enter, not just enter...

=TEXTJOIN(", ",TRUE,IF(ISNUMBER(SEARCH(List,$A2)),List,""),"")

Or, if you don't have TEXTJOIN on your Excel version, let's try the following:

Control+shift+enter, not just enter, and copy down:

=REPLACE(aconcat(IF(ISNUMBER(SEARCH(List,A2)),", "&List,""),""),1,2,"")

For this formula to work, add the following ​

<acronym title="visual basic for applications" style="background-color: transparent; border-bottom-color: rgb(0, 0, 0); border-bottom-style: dotted; border-bottom-width: 1px; border-image-outset: 0; border-image-repeat: stretch; border-image-slice: 100%; border-image-source: none; border-image-width: 1; border-left-color: rgb(51, 51, 51); border-left-style: none; border-left-width: 0px; border-right-color: rgb(51, 51, 51); border-right-style: none; border-right-width: 0px; border-top-color: rgb(51, 51, 51); border-top-style: none; border-top-width: 0px; color: rgb(51, 51, 51); cursor: help; font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; word-spacing: 0px;">VBA</acronym>

code to your workbook as a module, using Alt+F11...

Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
Dim y As Variant
If TypeOf a Is Range Then
For Each y In a.Cells
aconcat = aconcat & y.Value & sep
Next y
ElseIf IsArray(a) Then
For Each y In a
aconcat = aconcat & y & sep
Next y
Else
aconcat = aconcat & a & sep
End If
aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function​

<strike style="background-color: transparent; color: rgb(51, 51, 51); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; orphans: 2; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; word-spacing: 0px; word-wrap: break-word;"></strike>

 

[Peter_SSs]

Dear Sir,

Apology to restart the Thread. Sir, is it possile to get result in Cdolumn B with comma after each result example below.

VBA can also be do.

Yet another duplicate to https://www.mrexcel.com/forum/excel-questions/893600-extract-multiple-keywords-text-string-post5329886.html#post5329886

Please do not post the same question multiple times. Questions of a duplicate nature will be locked or deleted, per #12 of the Forum Rules and points 6 & 7 of the Forum Use Guidelines.

Any bumps, clarifications, or follow-ups should be posted to the linked thread.