SOLVED

# If column has a word within it pull the name in another column

Brass Contributor

# If column has a word within it pull the name in another column

Hi there,

I have the below in a ss in columns A,B,C respectively. In another tab I want to have the column heading be a city. ie. Windsor. and I want to pull the first and last names of anyone who says windsor (see 2nd ss below) in the list below. If there is no data then to leave it blank or write "NA". Can someone help me please? Not sure if it can be a pivot table because there is multiple cities in 1 cell

 First Last City Adam Elliott Sarnia, London, Windsor Lisa Ferreira London

10 Replies

# Re: If column has a word within it pull the name in another column

=IF(ISNUMBER(SEARCH(E\$1,\$C2)),\$A2&" "&\$B2,"")

This could be a simple solution if you create the column headings with the help of text to columns and remove duplicates and transpose. The formula is in cell E2 and filled across range E2:J5 in the example. For illustration the tables are in the same spreadsheet.

# Re: If column has a word within it pull the name in another column

In A2 on the second sheet:

=FILTER('People Sheet'!\$A\$2:\$A\$200&" "&'People Sheet'!\$B\$2:\$B\$200, ISNUMBER(SEARCH(A1, 'People Sheet'!\$C\$2:\$C\$200)), "")

Replace People Sheet with the real name of the first sheet, and adjust the ranges as needed.

Then fill to the right.

# Re: If column has a word within it pull the name in another column

Another alternative could be Power Query. In the attached file you can add data to the blue dynamic table. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table.

The data layout in the screenshot and in the attached file is for illustration. You can as well place the green result table in another worksheet.

# Re: If column has a word within it pull the name in another column

Thanks Oliver...this would be great but I get this error when I try to refresh
[Expression.Error] we cannot convert the value null to type Text

# Re: If column has a word within it pull the name in another column

The expression error occurs if no city is entered for a first and last name.

If it should be possible to enter first and last name without at least one city then the query must be changed accordingly. For example with a changed query the result could look like in the screenshot below where the first and last name is entered under "no city entered" if there isn't an entry for city in the blue table.

# Re: If column has a word within it pull the name in another column

The city is filled out, but still getting error

# Re: If column has a word within it pull the name in another column

Can you attach your file without sensitive data? Another strange thing in your file is that the M code returns "Adam Example" while "Ann" and "Example" was entered. In the last three rows of the blue table there is no data entered. Without seeing your file and code i can't help you i'm afraid. Alternatively you can enter the data from your file in the file i already sent you and run the query.

# Re: If column has a word within it pull the name in another column

@OliverScheurich attached below. I just downloaded yours that you sent and changed Adam to Ann and added another row and then clicked refresh.

best response confirmed by HansVogelaar (MVP)
Solution

# Re: If column has a word within it pull the name in another column

The file you sent is my first suggestion where all data must be entered. Attached is the file with the changed M code which accepts rows without a city. I changed the name of the file from "If column has a word within it pull the name in another column" to "CHANGED If column has a word within it pull the name in another column" for easier identification.

# Re: If column has a word within it pull the name in another column

AMAZING!! Thank you so much, this is perfect!
1 best response

Accepted Solutions
best response confirmed by HansVogelaar (MVP)
Solution

# Re: If column has a word within it pull the name in another column

The file you sent is my first suggestion where all data must be entered. Attached is the file with the changed M code which accepts rows without a city. I changed the name of the file from "If column has a word within it pull the name in another column" to "CHANGED If column has a word within it pull the name in another column" for easier identification.