How to search multiple value in a single cell in excel

June 23, 2016 at 09:40:36
Specs: Windows 7
there is an address in cell A1 containing two locality and in another sheet column A contains database of locality. My question is how to find more than one locality from certain address.

message edited by BipinBisht


See More: How to search multiple value in a single cell in excel

Report •

#1
June 23, 2016 at 10:03:23
Can you provide an example of what the cell contens will look like and what the 'database' looks like too?

Report •

#2
June 25, 2016 at 10:35:06
Suppose here is an address in cell A like: Office no 3, Devika Tower, Nehru Place, near Kalkaji, New Delhi, Delhi and Database in column B like: Govindpuri, Nehru Place, Kalkaji, Rajendra Place, etc. in vertical, and my question was how to extract Nehru Place and Kalkaji from address in other cell. So please provide me solution for this problem.

message edited by BipinBisht


Report •

#3
June 27, 2016 at 00:41:32
So here is what I think your workbook looks like and the code to find parts of an address within a string

Sheet1

          A
1        Devika Tower, Nehru Place, near Kalkaji, New Delhi, Delhi


Sheet2 (DataBase)

           A     B
1                Govindpuri
2                Nehru Place
3                Kalkaji
4                Rajendra Place

The code below will loop through all of the items in column B of database and look for them in A1 of sheet1. If there is a match it will return the item which was found.

Sub FindAddress()
    
    Dim FindVal As String
    Dim SearchString As String
    Dim Bcell As Range
    
    For Each Bcell In Sheet2.Range("B1", Sheet2.Range("B" & Rows.Count).End(xlUp))
        If InStr(1, Sheet1.Range("A1"), Bcell.Value) > 0 Then
            
            Debug.Print Bcell.Value
        End If
    Next Bcell
        
End Sub

message edited by AlwaysWillingToLearn


Report •

Related Solutions

#4
June 27, 2016 at 11:11:05
Macro is not working please explain me how I will get output because there is no output after running macro...

Report •

#5
June 27, 2016 at 12:04:16
I'm jumping in here...

The macro is set up to show the results in the Immediate Window of the VBA editor, which it indeed does.

Where do you want the result to appear?

What did you mean by "how to extract Nehru Place and Kalkaji from address in other cell"? Extract to where?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#6
June 28, 2016 at 21:18:56
The solution is to to create a formula that can test for multiple values and return a list of TRUE / FALSE values. Once we have that, we can process that list (an array, actually) with SUMPRODUCT.

The formula we're using looks like this:

=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))>0


Report •

#7
June 28, 2016 at 23:54:35
Bipin,

as Derby said the output is displayed in the immediate window, within the VBE IDE click on view then select 'Immediate Window' then run the code, you will see if the output there.

aren111,
I don't know if your solution will work, the OP has asked to extract the parts of the address rather than confirmation weather they exist in the string or not.


message edited by AlwaysWillingToLearn


Report •

Ask Question