Excel / Google sheet formula. Help me before I lose it completely!

[JoanThursday]

Formula aces, I need your help!

I have a spreadsheet with a bunch of names in a particular column (lets call it A1). Some cells in this column have just one name (eg Brown, Bert (Mr)), where as others have several names (eg Green, Sam (Mrs), White, Ned (Mr), Black, Mary (Prof)). Layout of names in the cells is as shown in my examples.

I need to search the data in column A1 and find all the surnames that match the ones I have on a separate list. I'd like the formula to find instances of the names and put 'true' in a second column. If it find two names on the list in one particular cell, it needs only return 'true' once.

I tried to use =IF(OR(A1="Brown",A1="Green",A1="White"),"TRUE","FALSE") but it returned 'FALSE' each time, and it's obviously not correct. I can see the names are there!

I've also tried 'if ... then' and 'find' formulas but I'm getting myself in a right knot with the syntax.

Can anyone help before I throw the whole lot out the window?!

Could you do it as a v look up?

The problem is that your current formula will only find an exact match for the cell. I think you need something like this

@LeroyJenkinssss - yep, I figured that was the problem. I need to find part of a string rather than than the contents of the entire cell.

I'll have a go with the info you found.

@Kfjsjdbd - I tried that too, but obviously my syntax was totally off!

Can you not just sort the entire database into alphabetical order?

Ah, back to the same problem. How do I write the formula so that it searches each cell for multiple names and then returns'TRUE' or 'FALSE'? That's where I'm tripping up. It tells me that I have a parsing problem but not where ...

Hmm, a tricky one. A quick google suggests ISTEXT might work?

@toppkatz

Can you not just sort the entire database into alphabetical order?

No, that won't work unfortunately, because there can be multiple names in each cell with each separated by a comma.

The data has come from another source which can't be sorted alphabetically before I export it into Excel.

I originally split each name out into a cell on its own, but it's making the pivot table I want to run very complicated if I do that.

How about if you use the text to column option on your A1 column, choosing the comma as a separator? Then you have each word in A1 in a separate column and you can use an IF or a Vlookup on each of the resulting columns to find your matches

Please disregard my previous post, THIS is what you need

www.excel-easy.com/examples/contains-specific-text.html

@MindyStClaire

Hmm, a tricky one. A quick google suggests ISTEXT might work?

@MindyStClaire, yes I found that on Google too.

This is what I'm trying to say:

Find Brown OR White OR Red OR Black in column A1. If you find any of these words, put 'TRUE' in column A2.

So, I'm not matching the full contents of a cell, but a short string within that cell, if that makes sense! This is where I'm tripping up.

I'd try using one of the COUNT arguments - probably COUNTIF but haven't crunched data in Excel for a while so little out of practice.

Ah, sorry COUNT will literally give numbers instead of True/False arguments. Missed that necessary bit.

@HacerSonarSusPasos - that looks promising.

It's just how I write it so that it checks each cell against a list of multiple names, and not just one. Still scratching my head on that one!

Not exactly the same, but if you apply a filter to the sheet, and then on the column you are working on, go to search and type in the name you are looking for. It will then bring up all the cells which contain that name anywhere in the cell.

Ha! I might indeed go down that route, @IsolaPribby. Could have bluddy done it by now.

I still need to get 'TRUE' into a new column, because I need to pull the information into a pivot table.

I would text to column to get all the surnames in their own cells and then you can do if or multiple times

I don't think you can do if like sadly which is what you need

You could do a nested if statement so if search for first name = true, return true, if false search for next name etc etc

@JoanThursday just make several more columns and put a true in there every time you get the results from your search. So you will have a column for True for black, another for true for brown etc. Then you can combine those results into one column which says true where any of the others are. Does that make sense?

Or just copy the names values into another column (on another tab), remove duplicates and then write true against the rows that have the data you are looking for. Then vlookuo the true values back into your first sheet. It is clunky though

Actually www.ablebits.com/office-addins-blog/2017/10/18/excel-if-cell-contains-formulas/ might do it.

Thanks for all your help. I'm sure it must be possible, but the formula is just seems too complicated for the time / patience I've got.

So, back to the 'text to column' approach for each of the names. Some cells have 6 names and some have only one, so it will complicate the table AND the pivot.

But ... gah. Like I say, I'm losing patience!

You should be able to nest using the above too to do once per string

The section called

If cell contains one of many text strings (OR logic) will do it

Yes, I found that page @LefttoherownDevizes. I've bookmarked it because it's so useful!

Buuuuut .... I have 15 names to check against each cell. It's going to be a bloody complicated nested statement.

@IsolaPribby - exactly what I'm going to do!

And thank you all

I knew there would be tons of ideas!