|
Searching for partial text match in range
I am trying to search a partial text match given a range, in this case
three columns, and return a true false answer as to whether or not the data can be found. Column A is a given users name and colums B, C, and D are variations of names and logins from various programs. I am trying to get column E to have a true/false value. This is kind of what the sheet looks like: A B C D NameFul Valor_Users ASA _User Now Users Abatta, Josef ABBOTT Abdail Abu Accordino Abdul, Rasad S. ABDUL ADIM MIZA Adams Apu, Andrew S. AELE ALEN GREGG Allred Ackenhil, Jenifer L. ABELOE ALAN EDDINGS Anderson Ackerman, Brian J. ABNDROTH Allen Eddings Anderion Acqaviva, Katja . ACUFF ALLEN S WAREN Andresen Adams, Elise L. ACUNA AMANDA DRUMOD Ashworth Adon, Dian ADAIS ANDRE J PAUIN Barbour How can I do a partial search, for say just the last name of column A, so all information up to the comma, and then compare it across the entire range of b,c and d, and return a true false response in coumn E? Is this possible? Any help would be VERY much appreciated. I apologize for the terrible formatting. |
Searching for partial text match in range
Try this:
=COUNTIF(B$2:D$9,"*"&LEFT(A2,FIND(",",A2)-1)&"*")0 Biff wrote in message oups.com... I am trying to search a partial text match given a range, in this case three columns, and return a true false answer as to whether or not the data can be found. Column A is a given users name and colums B, C, and D are variations of names and logins from various programs. I am trying to get column E to have a true/false value. This is kind of what the sheet looks like: A B C D NameFul Valor_Users ASA _User Now Users Abatta, Josef ABBOTT Abdail Abu Accordino Abdul, Rasad S. ABDUL ADIM MIZA Adams Apu, Andrew S. AELE ALEN GREGG Allred Ackenhil, Jenifer L. ABELOE ALAN EDDINGS Anderson Ackerman, Brian J. ABNDROTH Allen Eddings Anderion Acqaviva, Katja . ACUFF ALLEN S WAREN Andresen Adams, Elise L. ACUNA AMANDA DRUMOD Ashworth Adon, Dian ADAIS ANDRE J PAUIN Barbour How can I do a partial search, for say just the last name of column A, so all information up to the comma, and then compare it across the entire range of b,c and d, and return a true false response in coumn E? Is this possible? Any help would be VERY much appreciated. I apologize for the terrible formatting. |
Searching for partial text match in range
Biff,
If there anyway to modify it so that it will only search for the last name from Column A. So essentially everything up until the comma, but nothing after? because this returns 0 for everything still. Thanks for your help. Biff wrote: Try this: =COUNTIF(B$2:D$9,"*"&LEFT(A2,FIND(",",A2)-1)&"*")0 Biff wrote in message oups.com... I am trying to search a partial text match given a range, in this case three columns, and return a true false answer as to whether or not the data can be found. Column A is a given users name and colums B, C, and D are variations of names and logins from various programs. I am trying to get column E to have a true/false value. This is kind of what the sheet looks like: A B C D NameFul Valor_Users ASA _User Now Users Abatta, Josef ABBOTT Abdail Abu Accordino Abdul, Rasad S. ABDUL ADIM MIZA Adams Apu, Andrew S. AELE ALEN GREGG Allred Ackenhil, Jenifer L. ABELOE ALAN EDDINGS Anderson Ackerman, Brian J. ABNDROTH Allen Eddings Anderion Acqaviva, Katja . ACUFF ALLEN S WAREN Andresen Adams, Elise L. ACUNA AMANDA DRUMOD Ashworth Adon, Dian ADAIS ANDRE J PAUIN Barbour How can I do a partial search, for say just the last name of column A, so all information up to the comma, and then compare it across the entire range of b,c and d, and return a true false response in coumn E? Is this possible? Any help would be VERY much appreciated. I apologize for the terrible formatting. |
Searching for partial text match in range
Just remove the wildcards "*"
=COUNTIF(B$2:D$9,LEFT(A2,FIND(",",A2)-1))0 Biff wrote in message oups.com... Biff, If there anyway to modify it so that it will only search for the last name from Column A. So essentially everything up until the comma, but nothing after? because this returns 0 for everything still. Thanks for your help. Biff wrote: Try this: =COUNTIF(B$2:D$9,"*"&LEFT(A2,FIND(",",A2)-1)&"*")0 Biff wrote in message oups.com... I am trying to search a partial text match given a range, in this case three columns, and return a true false answer as to whether or not the data can be found. Column A is a given users name and colums B, C, and D are variations of names and logins from various programs. I am trying to get column E to have a true/false value. This is kind of what the sheet looks like: A B C D NameFul Valor_Users ASA _User Now Users Abatta, Josef ABBOTT Abdail Abu Accordino Abdul, Rasad S. ABDUL ADIM MIZA Adams Apu, Andrew S. AELE ALEN GREGG Allred Ackenhil, Jenifer L. ABELOE ALAN EDDINGS Anderson Ackerman, Brian J. ABNDROTH Allen Eddings Anderion Acqaviva, Katja . ACUFF ALLEN S WAREN Andresen Adams, Elise L. ACUNA AMANDA DRUMOD Ashworth Adon, Dian ADAIS ANDRE J PAUIN Barbour How can I do a partial search, for say just the last name of column A, so all information up to the comma, and then compare it across the entire range of b,c and d, and return a true false response in coumn E? Is this possible? Any help would be VERY much appreciated. I apologize for the terrible formatting. |
| All times are GMT +1. The time now is 07:50 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com