• 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.

Extract a name from a text using a standard list of names

luis_marques

luis_marques

Member
Hi,

I would like to extract a given name and the surname from a text (a column of texts) using to compare a standard list of given names to identification (another column .

Example:

1. Text as original
Distribuidora Gaúcha de Energia José da Silva Filho

2. Given name and surname i want to extract
José da Silva Filho

3. Text bolding the given name to be compared with a list (column) of given names for appropriated identification
Distribuidora Gaúcha de Energia José da Silva Filho

4. Standard list of given names (example)

Alberto
Carlos
José
Lúcio

Best regards.

Luis
 
Maybe something like this?
Pls check the file


This just for the sample U givin
 

Attachments

  • Search Names.xlsx
    10.1 KB · Views: 7
@azumi

Bit modified your formula to
=MID(A1,SEARCH(LOOKUP(2*15,SEARCH($G$2:$G$4,SUBSTITUTE(A1," ","")),$G$2:$G$4),A1),LEN(A1))

No need to find "," in red portion as it is not there.

Regards,
 
Yes, it worked for this example. But i am sure i wasn't enough clear with you guys. What i mentioned as "text as original" has variable length. So when i dropdown it doesn't work. Example:

Distribuidora Gaúcha de Energia José da Silva Filho
AES Tietê S/A Carlos José Filho
AES Uruguaiana Empreendimentos Ltda. Lúcio Costa
AETE - Amazônia - Eletronorte Transmissora de Energia S/A Sebastião Caetano

I need to find and extract José, Carlos and Lúcio full name.

Best regards.
 
Hi,

Can you just upload a sample file with i/p & o/p, it is just relax somebody who will answer your query from making one.

Regards,
 
@luis_marques

Try below formula in B1 and copy down: Data in A1, Lookup range of names in G2:G4.

=IFERROR(MID(A1,SEARCH(INDEX($G$2:$G$4,MATCH(1,--ISNUMBER(SEARCH($G$2:$G$4,SUBSTITUTE(A1," ",""))),0)),A1),LEN(A1)),"-")

Note: This is an array formula, So must be entered with Ctrl+Shift+Enter.

Regards
 
Dear Somendra,

Is possible to sophisticate solution in this manner: if it identify two given names in the same row which are also in the list, extract just from the first in the text?

B.R.

Luis
 
I attached a sample.

In the first row there are two given names that in portuguese is considered a given name composed. The formula is reading from the second. I would like to read from the first found.

Best regards.
 

Attachments

  • SAMPLE2.xlsx
    9.8 KB · Views: 2
Last edited:
@luis_marques

I still not understood what your requiremnent is? Kindly upload a file with sample data and required output, name list and any other information that can be used to solve the problem.

Regards,
 
@luis_marques

There is a mistake in your sample data, in A6 you have
Alcoa Alumínio S/A Fillipe Henrique Neves Soares

Where as you want output as
Filipe Henrique Neves Soares
So Filipe has an extra L in data, if I change it to Filipe which is there in your list and apply below formula it is giving expected result. Just try this:

=IFERROR(MID(A1,MIN(IFERROR(SEARCH($G$2:$G$410,A1),99999999)),LEN(A1)),"-")

Here name list is in G2:G410. Data in column A put formula in Column B.

Note this is an array formula so must be entered with Ctrl+Shift+Enter and not Just Enter.

Just advise if any issue.

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