• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

If(ISNUMBER(SEARCH limits Need a work around or other option

ireland13752

ireland13752

Member
Good Morning.

I am working on a complex formula that I may have found a fix for, but am not sure if/how to apply it.

I have data that comes out of another program as a column collection (meaning that it takes several rows of data and combines them into one cell in Excel.) this is causing an issue in that I have to look for specific detail in that cell to see if it qualifies for something else. I built a complex nested If(ISNUMBER(SEARCH statement, however I have surpassed the limitations of the formula. I really do not want to have to build a repeating filter Macro to accomplish this task, but if I cannot find an alternative I will have to.

Here are some examples and the formula.

This detail is all in one cell in a column. I need to be able to look in here and see if only a select list of codes are in here, because not all will apply. The formula is below.

45.71 Opn mul seg lg
46.10 Colostomy NOS
53.51 Incisional hernia 54.21 Laparoscopy
92.29 Radiotherapeut EC
54.25 Peritoneal lavage
99.29 Inject/infuse NEC

Formula:
=IF(ISNUMBER(SEARCH("*17.39*",U4)),"17.39",IF(ISNUMBER(SEARCH("*17.31*",U4)),"17.31",IF(ISNUMBER(SEARCH("*17.32*",U4)),"17.32",),IF(ISNUMBER(SEARCH("*17.33*",U4)),"17.33",IF(ISNUMBER(SEARCH("*17.35*",U4)),"17.35",IF(ISNUMBER(SEARCH("*17.34*",U4)),"17.34",IF(ISNUMBER(SEARCH("*17.36*",U4)),"17.36",IF(ISNUMBER(SEARCH("*45.03*",U4)),"45.03",IF(ISNUMBER(SEARCH("*45.26*",U4)),"45.26",IF(ISNUMBER(SEARCH("*45.41*",U4)),"45.41",IF(ISNUMBER(SEARCH("*45.49*",U4)),"45.49",IF(ISNUMBER(SEARCH("*45.52*",U4)),"45.52",IF(ISNUMBER(SEARCH("*45.71*",U4)),"45.71",IF(ISNUMBER(SEARCH("*45.76*",U4)),"45.76",IF(ISNUMBER(SEARCH("*45.79*",U4)),"45.79",IF(ISNUMBER(SEARCH("*45.81*",U4)),"45.81",IF(ISNUMBER(SEARCH("*45.82*",U4)),"45.82",IF(ISNUMBER(SEARCH("*45.83*",U4)),"45.83",IF(ISNUMBER(SEARCH("*45.92*",U4)),"45.92",IF(ISNUMBER(SEARCH("*45.93*",U4)),"45.93",IF(ISNUMBER(SEARCH("*45.94*",U4)),"45.94",IF(ISNUMBER(SEARCH("*45.95*",U4)),"45.95",IF(ISNUMBER(SEARCH("*46.03*",U4)),"46.03",IF(ISNUMBER(SEARCH("*46.04*",U4)),"46.04",IF(ISNUMBER(SEARCH("*46.10*",U4)),"46.10",IF(ISNUMBER(SEARCH("*46.11*",U4)),"46.11",IF(ISNUMBER(SEARCH("*46.13*",U4)),"46.13",IF(ISNUMBER(SEARCH("*46.14*",U4)),"46.14",IF(ISNUMBER(SEARCH("*46.43*",U4)),"46.43",IF(ISNUMBER(SEARCH("*46.52*",U4)),"46.52",IF(ISNUMBER(SEARCH("*46.75*",U4)),"46.75",IF(ISNUMBER(SEARCH("*46.76*",U4)),"46.76",IF(ISNUMBER(SEARCH("*46.94*",U4)),"46.94",IF(ISNUMBER(SEARCH("*44140*",U4)),"44140",IF(ISNUMBER(SEARCH("*44141*",U4)),"44141",IF(ISNUMBER(SEARCH("*44143*",U4)),"44143",IF(ISNUMBER(SEARCH("*44144*",U4)),"44144",IF(ISNUMBER(SEARCH("*44145*",U4)),"44145",IF(ISNUMBER(SEARCH("*44146*",U4)),"44146",IF(ISNUMBER(SEARCH("*44147*",U4)),"44147",IF(ISNUMBER(SEARCH("*44150*",U4)),,"44150",IF(ISNUMBER(SEARCH("*44151*",U4)),"44151",IF(ISNUMBER(SEARCH("*44160*",U4)),"44160",IF(ISNUMBER(SEARCH("*44204*",U4)),"44204",IF(ISNUMBER(SEARCH("*44205*",U4)),"44205",IF(ISNUMBER(SEARCH("*44206*",U4)),"44206",IF(ISNUMBER(SEARCH("*44207*",U4)),"44207",IF(ISNUMBER(SEARCH("*44208*",U4)),"44208",IF(ISNUMBER(SEARCH("*44210*",U4)),"44210","")))))))))))))))))))))))))))))))))))))))))))))))))

I found this option, but not really sure how the "&" changes things and if there would be limitions on this either....

=IF(ISNUMBER(SEARCH("17.34",U4)),"17.34","") &
IF(ISNUMBER(SEARCH("17.36",U4)),"17.36","") &
IF(ISNUMBER(SEARCH("45.03,U4)),"45.03","") & etc....
I cannot use a VLookup because it is all in one cell, :(
Thank you for any help you may be able to offer....

Samantha
 
Hi Samantha ,

How many search codes are there ?
Will they be the same for all cells ?
Do you have this codes list in one place in your workbook ?

Narayan
 
This is the list for now; they will always be the same until January then they will change, and occasionally there maybe some additions and/or subtractions but for the most part they will always be the same.
I have a whole seperate list that I will do the same thing for but can apply the same logic once I find the resolution to this one. I will just change the list/codes and it should work the same. I do have the codes listed in one place in my workbook. They are below if that is helpful.
17.31
17.32
17.33
17.34
17.35
17.36
17.39
45.03
45.26
45.41
45.49
45.52
45.71
45.76
45.79
45.81
45.82
45.83
45.92
45.93
45.94
45.95
46.03
46.04
46.10
46.11
46.13
46.14
46.43
46.52
46.75
49.76
46.94
44140
44141
44143
44144
44145
44146
44147
44150
44151
44160
44204
44205
44206
44207
44208
44210
 
Hi Samantha ,

If only one code will appear in a cell , then a simple :

=LOOKUP(999,SEARCH(Codes,Cell),Codes)

will display the code which is present.

Codes is a named range referring to your codes list. Cell is a cell reference , say A1 , where A1 contains the text :

45.71 Opn mul seg lg
46.10 Colostomy NOS
53.51 Incisional hernia 54.21 Laparoscopy
92.29 Radiotherapeut EC
54.25 Peritoneal lavage
99.29 Inject/infuse NEC

Narayan
 
Can you clarify your requirement?

You are checking a given string for the list of codes.
If a given code is found, you want to return that found code value.

What should happen if multiple codes match? Return the first code found?
 
This is going into a Macro, ... I have to admit I am very uninformed as to VBA, so if I can add the code to the Macro then yes, or if you can help me with applying the VBA then yes... The only limitation with that is my ignorance of VBA, I think
 
This is not a one code per cell situation as I stated in my original post, so a lookup will not work.
If more than one code is found it does not matter which is returned so long as the one returned is in the required listing.
 
In that case, Narayan's solution above should work for you. Change 999 to len(cell) if needed.
 
Since you've said that you don't use VBA much, I have built up small workbook which should help your cause.

Here's the UDF which is commented so should help you in editing:
Code: 
'\\ Always goes in a standard module
Public Function SearchMatches(rngToSearch As Range, rngCriteria As Range) As String
Dim r As Range
'\\ Assign a null string to begin with
SearchMatches = vbNullString
'\\ Loop through all criteria cells to see if there are matches
For Each r In rngCriteria
    '\\ If we find something we add it to the original string
    If InStr(rngToSearch.Value, r.Value) > 0 Then
        SearchMatches = SearchMatches & " " & r.Value
    End If
Next r
'\\ Join data with commas rather than spaces
SearchMatches = Replace(Trim(SearchMatches), " ", ", ")
End Function

You don't have to add those search codes to macro. You can keep them in the workbook as usual.
 

Attachments

  • VBA - Use of UDF for concatenating results.xlsm
    17.3 KB · Views: 2
Okay, I think I am missing something, I put in the code =LOOKUP(LEN(U:U),SEARCH(A:A,U:U),A:A) With my list of codes to search for in Column A and my collection of codes to look for in column U, I got an #N/A. Would you like me to attach and example file?
 
Hi Samantha ,

The formula is supposed to match a list of codes with one cell , not an entire range of cells.

You will need to enter the formula I posted in one cell , and then copy it down.

=LOOKUP(999,SEARCH(Codes,Cell),Codes)

where Codes refers to a range of cells in one column , which contains your codes e.g. H1:H47 may contain 47 codes ; Cell refers to the cell which contains all of the text you posted , say A1.

Enter the formula in any unused column , say C1 , and then copy it down.

Narayan
 
Instead of full column references, can you refer to a specific set of cells for the codes?

Feel free to attach a workbook if that above suggestion does not make sense.
 
I entered this
=LOOKUP(999,SEARCH(A:A,U2),A:A)
With my list being in column A and my combined data in column U and my lookup in cell D2... I got a 0 as my returned value. And I can see the value listed there....

I am sorry if I am being obtuse, I have a bit of a headache and am not thinking at 100%...
 
See attached... with Narayan's formula added.

The cells with errors are those where a code was not found. You can use IFERROR(...) to handle them, if needed.
 

Attachments

  • Chandoo_file-Ireland-Codes.xlsx
    16.2 KB · Views: 20
You must log in or register to reply here.
Back
Top