Need to seperate Data in one cell

[Tyler.Rains]

I have a cell which has 2 names in it. I need a way to keep both names in the same cell but for excel to recognize it as separate names. I can't manipulate the cells or the sheet in any way. I just need some sort of comma separation or something so it recognizes both names as individuals instead of 1.

Example:

A3 contains 2 names Mike Smith, John Doe

Excel is recognizing the name as Mike Smith, John Doe

I need it to recognize it as Mike Smith and John Doe.

This data is exported to a separate sheet within the workbook. The names have to match exactly for the countif formula to work.

Thanks for the help

[NBVC]

Something like?

=COUNTIF("*"&X1&"*",A:A)

where X1 can contain Mike Smith, and column A may contain Mike Smith, John Doe. This will count how many cell contain Mike Smith, ignoring John Doe

[Tyler.Rains]

I need it to recognize both names. This situation only comes up once in a while. The actual situation is my guys submit a report to me about which supervisors they visited in the field that day. Sometimes on the date line they have visited more than one supervisor in a day so they will enter multiple names into the cell. I can't change either document or add additional cells because it is a corporate document.

[NBVC]

I can't believe I had that arguments reversed in that formula !! should've been =COUNTIF(A:A,"*"&X1&"*")

Anyway this formula will count number of times names in X1 and X2 are in column A, is that more like what you need?

=SUMPRODUCT(COUNTIF(A:A,"*"&X1:X2&"*"))

[Tyler.Rains]

Supervisor Tracking 2013.xlsx

You can see here in the January sheet when there is only one name entered it automatically populates the data to the WSL sheet. In February where there are 2 names on the cell it does not.

[NBVC]

Then my first formula was the right idea, although I had the arguments reversed. I corrected it in last post...

Try in Feb's column, E2:

=COUNTIF(Feb!D$1:D$1048575,"*"&A2&"*")

copied down.

[TMS]

Please Login or Register  to view this content.

Regards, TMS

[Tyler.Rains]

That's half my battle. That gets the sheet to recognize Aaron Hoffus but it does not recognize the 2nd name. I need it to recognize both individually

[Tyler.Rains]

Sorry guys,

Had a brain fart there. I got it.

Thanks so much for your help

[TMS]

You're welcome.


If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.