• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Find a list of words in a cell

R

RMajare

New Member
Hello, I am trying to find a list of words in a cell. Do anyone know if it is possible without high level programming?

Here is the details:

I have a list of names like "Scott, Andy, Jennifer, Bridget, Bill, Tom, Hans...." This has around 200 names.


I have cells full of comments and I want to pull those names to next column

A Column B Column

"...Scott and Andy loves Jennifer..." Scott, Andy, Jennifer

"...Hans is the only guy lives here..." Hans

"...There is no name in this sentence..." -
 
Do you need a complete list, or jsut the fact that one of the names exist there? Having to list all of the names could get pretty tricky because there's no easy way to concatenate the results.
 
Yes I need complete list. I already have list of names. I just want excel to search all names in a cell and write those names to next column.
 
Welcome to Chandoo_Org forums.


Combining the data in one column will be tricky.


Here's an alternative you may want to consider.

https://www.box.com/s/dphvpz4ma3u6akkitdfk


The formula in B2 is an ARRAY formula which needs to be CTRL + SHIFT + ENTER and not just ENTER:

=IFERROR(INDEX(NameList,SMALL(IF(ISNUMBER(FIND(NameList,$A2,1)),ROW(NameList)),COLUMNS($B2:B2))),"-")

Copy down and across until you get "-".
 
Shrivallabha, That`s amazing and lightspeed quick!! Thanks! That would help me but I think there is a mistake in your sheet. A3 has only Hans but it writes Terry to B3.


Also It does not write Scott to A2.
 
Hi, RMajare!

Try reassigning the NameList named range from Sheet1!$J$2:$J$7 to Sheet1!$J$1:$J$7, as ROW(NameList) retrieves the array {2;3;4;5;6;7} instead of {1;2;3;4;5;6;7}.

In B3 displays Terry instead of Hans, which is incorrect too. It's the same shifted-by-1 issue.

Regards!


@shrivallabha

Hi, buddy!

Very nice solution indeed. Hope you don't mind about my intrusion or uninvited intervention. Just to add my two cents.

Regards!
 
@Shrivallabha... Good solution.


@RMajare... try this formula (array entered) instead.


=IFERROR(INDEX(NameList,SMALL(IF(ISNUMBER(FIND(NameList,$A2,1)),ROW(NameList)),COLUMNS($B2:B2))-1),"-")
 
Thank you guys for your appreciation. SirJB7, I believe more the help better it is!


I didn't notice the error. While posting it here, I thought I'd better assign some headings so inserted a row and that changed the game. So delete Row 1 completely and it will start working...! I will be careful next time.
 
@shirvallabha

Hi!

Don't even mention it... I was just passing, stepped into, and played a little with your toys. :)

Regards!
 
Sorry to jump in but I had a question on this too. This almost worked for me but what I run into when there are two similar names but I need it to treat them separately. In RMajare's example, what would happen if one cell had Hans but another cell had Hansel. Or Rob & Robert?


Thanks!
 
Hi ,


I assume you want to have the correct result when the list of names has a name like Hans , while the cells containing comments have a name like Hansel ; Hans should not match with Hansel.


For such a case , use the following formula , entered as an array formula , using CTRL SHIFT ENTER :


=IFERROR(INDEX(NameList&" ",SMALL(IF(ISNUMBER(FIND(NameList&" ",$A2,1)),ROW(NameList)-1),COLUMNS($B2:B2))),"-")


Since it is expected that a name will be separated from the next word by a space character , this will work in most cases except when the name is the last word in a comment , when it is followed by a period ( fullstop ).


Narayan
 
@NARAYANK991

Hi!

Tried this?

=IFERROR(INDEX(NameList&" ",SMALL(IF(ISNUMBER(FIND(SUBSTITUTE(Namelist&" ","."," .")&" ",$A2,1)),ROW(NameList)-1),COLUMNS($B2:B2))),"-")

It should fix up the followed-by-dot and last word issues, I think.

Regards!
 
I'm still working on this but haven't gotten the results I need. I'm tweaking the formula to try to solve it myself. I have a similar request as the one above except that I'm searching for codes and some codes are very similar:


A few examples of codes:

RE

RES

SCH

CAV


I'll upload a file if I can't make this work tonight.


Thanks all!


Mark
 
Hi, markdeuel!

I know all the concerns about privacy and sensitive data but for this kind of problems it's of great help counting with the actual data, as to analyze what issues will you be facing when you try to apply the suggested solutions to your real case.

Regards!
 
Thanks sir, it's more about wanting to do it myself but I'll review the acceptable ways to upload a file and upload it for the group.


Mark
 
I'm looking for something that will search in the attribute column and produce the full name of the code in the next column over. I have a list of attributes that can be in the Attribute column.


The issue I run into is when two codes are similar (RE and RES) in the attribute column but they mean entirely different things. I have not been able to figure out how to look for exact matches.


All codes are separated by a column except for the last one which won't have anything at the end.


https://docs.google.com/file/d/0Bzf24jxfRhGNT2t2R2oySHFtRzQ/edit

Thanks!

[pre]
Code: 
Attribute       Full Name
RES,FR,UN	University
RES,RE	        Regent
RES,FR,RE	Regent
RES,FR,CAV	Cavalier
[/pre]
 
Hi, markdeuel!


Please try to clarify my doubts:

a) the list of codes at column I is the entire list?

b) each code at column A is supposed to produce an unique and independent name or this name is produced by the combination of codes? i.e., row 3, "CAV,FR,NONE,RES" should generate "name 1", "name 2", "name 3" and "name 4", or just "name x"?

c) which is the actual issue between "RES" and "RE"? they're different and if you search for them as "RES," and "RE," they couldn't be equal in any way


Regards!
 
Okay, thanks a bunch. Unfortunately I'll be leaving for a few hours here shortly but I really appreciate your help and willingness to look at this.


Mark
 
Answers:


A) no it's not the entire list

B) Name X - in row 3 it should produce CAV in Column B (the attributes column will only have one of the attributes listed in column I)

C) When I was trying to use the original formula, it would see the RES code and insert RE (that could be my error though)
 
Hi, markdeuel!


Give a look at this file:

https://dl.dropbox.com/u/60558749/Find%20a%20list%20of%20words%20in%20a%20cell%20-%20Attributes%20Example%20%28for%20RMajare%20at%20chandoo.org%29.xlsm


It uses a dynamic named range (CodeList for columns L:M), then replicates its values at row 1 of columns B:xxx (orange), and retrieves the matrix of matchs (yellow). At last it displays the related name at column K (green).


If the number of entries for the code list were much higher you may have to replace the formula at column K from this:

=SI.ERROR(BUSCARV(B2&C2&D2&E2&F2&G2&H2&I2&J2;CodeList;2;FALSO);"") -----> in english: =IFERROR(VLOOKUP(B2&C2&D2&E2&F2&G2&H2&I2&J2,CodeList,2,FALSE),"")

to this, using an UDF, user defined function:

=SI.ERROR(BUSCARV(MultiConcatenate(B2:J2);CodeList;2;FALSO);"") -----> in english: =IFERROR(VLOOKUP(MultiConcatenate(B2:J2),CodeList,2,FALSE),"")


This is the code for the UDF:

-----

[pre]
Code: 
Option Explicit

Public Function MultiConcatenate(psRng As Range) As String
' constants
' declarations
Dim I As Long, A As String
' start
A = ""
' process
With psRng
For I = 1 To .Cells.Count
A = A & .Cells(I).Value
Next I
End With
' end
MultiConcatenate = A
End Function
[/pre]
-----


Just advise if any issue.


Regards!
 
Hi Mark ,


If you don't mind a formula-based solution , try this :


1. Define a named range , call it Keywords_Range , and in the Refers To box , enter the range address =Sheet1!$I$2:$I$8


2. Define a named range , call it Comments_Range , and in the Refers To box , enter the range address =Sheet1!$A$2:$A$26


3. Select the range B2:B26 , and copy + paste the following formula :


=INDEX(Keywords_Range,MMULT(N(ISNUMBER(FIND(TRANSPOSE(Keywords_Range&","),Comments_Range&","))),ROW(INDIRECT("1:"&ROWS(Keywords_Range)))))


and press CTRL SHIFT ENTER.


The keyword NONE is not entered in your Keywords_Range , and hence you will get a #N/A error wherever this is present.


Narayan
 
You must log in or register to reply here.
Back
Top