Extract name from a column in different sheet

[Mr2017]

Hi

I have a list of addresses in column A of Sheet 1 in a file (please see the 9 rows below).

I will regularly need to populate the 'City' name in column B of Sheet 1, using the City names from column D of Sheet 2.

And I've been advised to do this manually, but I'm sure there's a formula that can do this!? Can anyone please advise?

Please find below sample data for Sheet 1 - there are only 9 rows (3 rows of 3 addresses).

I have also pasted data from Sheet 2 below (it's just column D, with the names of the Cities that I have to manually populate into column B of Sheet 1. I'd be grateful if anyone could help with this?

Sheet 1

Address	Location
24 Place des Reflets La Defense, Paris, France
24 Place des Reflets La Defense, Paris, France
24 Place des Reflets La Defense, Paris, France
50 E Randolph Street, Suite 7100, Chicago, Illinois, USA
50 E Randolph Street, Suite 7100, Chicago, Illinois, USA
50 E Randolph Street, Suite 7100, Chicago, Illinois, USA
10 St. James'S Square, London, United Kingdom
10 St. James'S Square, London, United Kingdom
10 St. James'S Square, London, United Kingdom

Sheet 2

			Paris
			Paris
			Paris
			Chicago
			Chicago
			Chicago
			London
			London
			London

 

[mikerickson]

Have you tried putting =Sheet2!D1 in a cell and dragging that down?

Its not clear if you want to move the values from sheet2 to column B of sheet1, or if you want the formula in column B to extract the city name from the cell in column A.

 

[Mr2017]

Hi Mike

Thanks for your response.

I'd like to formula in column B to extract the City name in column A (for Sheet 1), using the City data from Sheet 2.

The order of the Cities in column D of Sheet 2 aren't always in the same order of the data in Sheet 1. So I can't use the =Sheet2!D1 formula.

I'd like to use a formula that will check if the City in column D of Sheet 2 matches the City in the cells in column A of Sheet 1, and if there is a match, then populate column B of Sheet 1 with the text from column D in Sheet 2.

Does that clarify the question?

Thanks in advance.

 

[mikerickson]

If you want the city name extracted from column A, then =TRIM(MID(SUBSTITUTE(B7,",",REPT(" ",100)),100,100)) will return the text between the first two commas (the city name).

The values in Sheet2 have no effect on what city is in a cell of sheet1 column A. I don't understand the "using the City data from Sheet 2" part.

 

[Mr2017]

Hi Mike

Thanks again for the prompt response.

That's a good solution, but it doesn't work for the Chicago example above.

However, I've thought of a way of possibly doing it....

I'll copy and paste the Cities from column D in Sheet 2 into column J of Sheet 1.

Then I would like to amend the formula below, so that it checks if the text in cell J1 is in cell A1 in Sheet 1, and if it is, then return the text in J1, otherwise leave it blank.

Do you know how to amend this formula to do that? I would put this formula in cell B1 of Sheet 1.

=IF(ISNUMBER(SEARCH("Text in column J",A1)),"Text in column J","")

I've used it when searching for specific text, which I've typed in the inverted commas.

Your thoughts would be greatly appreciated!

Thanks in advance.

 

[mikerickson]

=IF(ISNUMBER(SEARCH(J1,A1)), J1,"")

 

[Mr2017]

Excellent!!

That worked!!!

Thank you very much!!

 

[sandy666]

another approach with Power Query and no duplicates

Address		Cities		Address	Cities
24 Place des Reflets La Defense, Paris, France		Paris		24 Place des Reflets La Defense, Paris, France	Paris
24 Place des Reflets La Defense, Paris, France		Paris		50 E Randolph Street, Suite 7100, Chicago, Illinois, USA	Chicago
24 Place des Reflets La Defense, Paris, France		Paris		10 St. James'S Square, London, United Kingdom	London
50 E Randolph Street, Suite 7100, Chicago, Illinois, USA		Chicago
50 E Randolph Street, Suite 7100, Chicago, Illinois, USA		Chicago
50 E Randolph Street, Suite 7100, Chicago, Illinois, USA		Chicago
10 St. James'S Square, London, United Kingdom		London
10 St. James'S Square, London, United Kingdom		London
10 St. James'S Square, London, United Kingdom		London

let
    Cities = Excel.CurrentWorkbook(){[Name="Table11"]}[Content],
    Address = Excel.CurrentWorkbook(){[Name="Table10"]}[Content],
    tblCities = Table.AddColumn(Address, "Cities", each Cities),
    Expand = Table.ExpandTableColumn(tblCities, "Cities", {"Cities"}, {"Cities"}),
    Contains = Table.AddColumn(Expand, "Contain", each if Text.Contains([Address], [Cities]) then [Cities] else null),
    NullOut = Table.SelectRows(Contains, each ([Contain] <> null)),
    Group = Table.Group(NullOut, {"Address", "Cities"}, {{"Count", each Table.RowCount(_), type number}}),
    RC = Table.RemoveColumns(Group,{"Count"})
in
    RC

 

[Mr2017]

Hi @mikerickson

I was just wondering this.

Do you know how to make that search function search for text in a whole column rather than one cell?

So, for example, let's say that cell A1 had the City 'Chicago' in it, but cell J1 didn't, it would return a blank.

But then if cell A1 had 'Chicago' in it, and the word 'Chicago' was found in column J, then cell B1 would return the matching word ie 'Chicago' from column J....

Hi @sandy666

Thanks for the proposed solution. But I'm not familiar with Power Query yet, so I'd have to learn how to use it, first!

 

[sandy666]

Thanks for the proposed solution. But I'm not familiar with Power Query yet, so I'd have to learn how to use it, first!

sure
more about PowerQuery aka Get&Transform

• What is PowerQuery (Get&Transform)
• Getting started with PQ
• PowerQuery Help
• PowerQuery M reference (erratum required)