Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compare One Cell to the First 10 Characters of Another Cell
I would like to compare one cell (A1) to the first 10 characters in a range
of cells (B1:B125). If there is a match, I would like it displayed in C1. Any help would be appreciated. -- Jill |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compare One Cell to the First 10 Characters of Another Cell
Say A1 contains a123456789
and B1 thru B12 contain: xxxxxxxxxxxx xxxxxxxxxxxx xxxxxxxxxxxx xxxxxxxxxxxx xxxxxxxxxxxx a123456789gjsjgfjhgrfwjh xxxxxxxxxxxx xxxxxxxxxxxx xxxxxxxxxxxx xxxxxxxxxxxx xxxxxxxxxxxx xxxxxxxxxxxx Then in C1 enter: =OFFSET($B$1,MATCH(A1,LEFT(B1:B12,10),1)-1,0) This is an array formula that is entered with CNTRL-SHFT-ENTER rather than just ENTER -- Gary''s Student - gsnu200773 "Jill" wrote: I would like to compare one cell (A1) to the first 10 characters in a range of cells (B1:B125). If there is a match, I would like it displayed in C1. Any help would be appreciated. -- Jill |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compare One Cell to the First 10 Characters of Another Cell
On Wed, 12 Mar 2008 16:59:00 -0700, Jill
wrote: I would like to compare one cell (A1) to the first 10 characters in a range of cells (B1:B125). If there is a match, I would like it displayed in C1. Any help would be appreciated. Not sure what you mean by "a match". Or what "it" is that you want displayed. If, by "a match", you mean to say that a match exists if the contents of A1 are duplicated somewhere within the first 10 characters in the range of column B; and if, by "it", you mean to display the contents of the cell in B that contained a1, then something like: =LOOKUP(2,1/SEARCH(A1,LEFT(B1:B125,10)),B1:B125) might be what you are looking for. If you mean something else, you'll have to be more specific. --ron |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compare One Cell to the First 10 Characters of Another Cell
Thanks so much for your quick responses. I will try to clarify with actual
data ... Say A1 contains PROVIDENCE MEM HOSP and B1 thru B12 contains PASO DEL NORTE SURGERY CENTER, EL PASO, TX PHYSICIANS HOSPITAL, EL PASO, TX PLAINS REGIONAL MED CTR-CLOVIS, CLOVIS, NM PRESBYTERIAN HOSPITAL, ALBUQUERQUE, NM PROVIDENCE MEM HOSP, EL PASO, TX R E THOMASON HOSPITAL, EL PASO, TX ROOSEVELT GENERAL HOSPITAL, PORTALES, NM ROSWELL REGIONAL HOSPITAL, ROSWELL, NM SOCORRO GENERAL HOSPITAL, SOCORRO, NM SOUTHERN NEW MEXICO SURG CTR SUITE, ALAMOGORDO, NM SOUTHWEST ENDOSCOPY, ALBUQUERQUE, NM ST JOSEPH HOSPITAL, ALBUQUERQUE, NM I want C1 to return PROVIDENCE MEM HOSP, EL PASO, TX Then, say A2 contains ROSWELL REGIONAL HOSP and B1 thru B12 contains the same data as listed above I want C2 to return ROSWELL REGIONAL HOSPITAL, ROSWELL, NM -- Jill "Ron Rosenfeld" wrote: On Wed, 12 Mar 2008 16:59:00 -0700, Jill wrote: I would like to compare one cell (A1) to the first 10 characters in a range of cells (B1:B125). If there is a match, I would like it displayed in C1. Any help would be appreciated. Not sure what you mean by "a match". Or what "it" is that you want displayed. If, by "a match", you mean to say that a match exists if the contents of A1 are duplicated somewhere within the first 10 characters in the range of column B; and if, by "it", you mean to display the contents of the cell in B that contained a1, then something like: =LOOKUP(2,1/SEARCH(A1,LEFT(B1:B125,10)),B1:B125) might be what you are looking for. If you mean something else, you'll have to be more specific. --ron |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compare One Cell to the First 10 Characters of Another Cell
On Wed, 12 Mar 2008 18:14:00 -0700, Jill
wrote: Thanks so much for your quick responses. I will try to clarify with actual data ... Say A1 contains PROVIDENCE MEM HOSP and B1 thru B12 contains PASO DEL NORTE SURGERY CENTER, EL PASO, TX PHYSICIANS HOSPITAL, EL PASO, TX PLAINS REGIONAL MED CTR-CLOVIS, CLOVIS, NM PRESBYTERIAN HOSPITAL, ALBUQUERQUE, NM PROVIDENCE MEM HOSP, EL PASO, TX R E THOMASON HOSPITAL, EL PASO, TX ROOSEVELT GENERAL HOSPITAL, PORTALES, NM ROSWELL REGIONAL HOSPITAL, ROSWELL, NM SOCORRO GENERAL HOSPITAL, SOCORRO, NM SOUTHERN NEW MEXICO SURG CTR SUITE, ALAMOGORDO, NM SOUTHWEST ENDOSCOPY, ALBUQUERQUE, NM ST JOSEPH HOSPITAL, ALBUQUERQUE, NM I want C1 to return PROVIDENCE MEM HOSP, EL PASO, TX Then, say A2 contains ROSWELL REGIONAL HOSP and B1 thru B12 contains the same data as listed above I want C2 to return ROSWELL REGIONAL HOSPITAL, ROSWELL, NM Perhaps: C1: =IF(A1="","",LOOKUP(2,1/SEARCH(A1,LEFT($B$1:$B$12,LEN(A1))),$B$1:$B$12)) and fill down as far as required. --ron |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compare One Cell to the First 10 Characters of Another Cell
WOO HOO -- YES!
Thank you so much!!! -- Jill "Ron Rosenfeld" wrote: On Wed, 12 Mar 2008 18:14:00 -0700, Jill wrote: Thanks so much for your quick responses. I will try to clarify with actual data ... Say A1 contains PROVIDENCE MEM HOSP and B1 thru B12 contains PASO DEL NORTE SURGERY CENTER, EL PASO, TX PHYSICIANS HOSPITAL, EL PASO, TX PLAINS REGIONAL MED CTR-CLOVIS, CLOVIS, NM PRESBYTERIAN HOSPITAL, ALBUQUERQUE, NM PROVIDENCE MEM HOSP, EL PASO, TX R E THOMASON HOSPITAL, EL PASO, TX ROOSEVELT GENERAL HOSPITAL, PORTALES, NM ROSWELL REGIONAL HOSPITAL, ROSWELL, NM SOCORRO GENERAL HOSPITAL, SOCORRO, NM SOUTHERN NEW MEXICO SURG CTR SUITE, ALAMOGORDO, NM SOUTHWEST ENDOSCOPY, ALBUQUERQUE, NM ST JOSEPH HOSPITAL, ALBUQUERQUE, NM I want C1 to return PROVIDENCE MEM HOSP, EL PASO, TX Then, say A2 contains ROSWELL REGIONAL HOSP and B1 thru B12 contains the same data as listed above I want C2 to return ROSWELL REGIONAL HOSPITAL, ROSWELL, NM Perhaps: C1: =IF(A1="","",LOOKUP(2,1/SEARCH(A1,LEFT($B$1:$B$12,LEN(A1))),$B$1:$B$12)) and fill down as far as required. --ron |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compare One Cell to the First 10 Characters of Another Cell
On Wed, 12 Mar 2008 18:48:01 -0700, Jill
wrote: WOO HOO -- YES! Thank you so much!!! -- Jill You're welcome. Glad to help. Thanks for the feedback. --ron |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compare One Cell to the First 10 Characters of Another Cell
Another one:
=VLOOKUP(LEFT(A1,10)&"*",B$1:B$12,1,0) Although, from the looks of your sample data you don't necessarily need to limit the lookup_value to the first 10 characters. This will work just as well with your sample data: =VLOOKUP(A1&"*",B$1:B$12,1,0) -- Biff Microsoft Excel MVP "Jill" wrote in message ... Thanks so much for your quick responses. I will try to clarify with actual data ... Say A1 contains PROVIDENCE MEM HOSP and B1 thru B12 contains PASO DEL NORTE SURGERY CENTER, EL PASO, TX PHYSICIANS HOSPITAL, EL PASO, TX PLAINS REGIONAL MED CTR-CLOVIS, CLOVIS, NM PRESBYTERIAN HOSPITAL, ALBUQUERQUE, NM PROVIDENCE MEM HOSP, EL PASO, TX R E THOMASON HOSPITAL, EL PASO, TX ROOSEVELT GENERAL HOSPITAL, PORTALES, NM ROSWELL REGIONAL HOSPITAL, ROSWELL, NM SOCORRO GENERAL HOSPITAL, SOCORRO, NM SOUTHERN NEW MEXICO SURG CTR SUITE, ALAMOGORDO, NM SOUTHWEST ENDOSCOPY, ALBUQUERQUE, NM ST JOSEPH HOSPITAL, ALBUQUERQUE, NM I want C1 to return PROVIDENCE MEM HOSP, EL PASO, TX Then, say A2 contains ROSWELL REGIONAL HOSP and B1 thru B12 contains the same data as listed above I want C2 to return ROSWELL REGIONAL HOSPITAL, ROSWELL, NM -- Jill "Ron Rosenfeld" wrote: On Wed, 12 Mar 2008 16:59:00 -0700, Jill wrote: I would like to compare one cell (A1) to the first 10 characters in a range of cells (B1:B125). If there is a match, I would like it displayed in C1. Any help would be appreciated. Not sure what you mean by "a match". Or what "it" is that you want displayed. If, by "a match", you mean to say that a match exists if the contents of A1 are duplicated somewhere within the first 10 characters in the range of column B; and if, by "it", you mean to display the contents of the cell in B that contained a1, then something like: =LOOKUP(2,1/SEARCH(A1,LEFT(B1:B125,10)),B1:B125) might be what you are looking for. If you mean something else, you'll have to be more specific. --ron |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Question on similar topic
I am currently searching for a method to do a similar formula in excel.
I currently process around 120 students every 2 weeks into classroom databases. Currently when we recieve the spreadsheet, the names of students show as: last, first middle initial in cells A1-A120. I was wondering if there was a way to create a formula to break this cell down into three cells one for first, one for last and one for middle inital. Example... Smith, John C. would be in cell A1 How could this be broken down into three seperate cells B1, C1 and D1? I tried basing this off the above suggestions but peoples name have different lengths so I need a way to seperate them by commas and periods. any suggestions would be appreciated. Thanks |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Question on similar topic
On Thu, 08 May 2008 10:43:08 -0700, Frank Sabella wrote:
I am currently searching for a method to do a similar formula in excel. I currently process around 120 students every 2 weeks into classroom databases. Currently when we recieve the spreadsheet, the names of students show as: last, first middle initial in cells A1-A120. I was wondering if there was a way to create a formula to break this cell down into three cells one for first, one for last and one for middle inital. Example... Smith, John C. would be in cell A1 How could this be broken down into three seperate cells B1, C1 and D1? I tried basing this off the above suggestions but peoples name have different lengths so I need a way to seperate them by commas and periods. any suggestions would be appreciated. Thanks Last Name: =LEFT(A1,FIND(",",A1)-1) First Name: =MID(A1,FIND(",",A1)+2,FIND(CHAR(1), SUBSTITUTE(A1," ",CHAR(1),2))-FIND(",",A1)-2) MI: =MID(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),2))+1,99) --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
FIND / SEARCH text compare cell to string in 3rd cell | Excel Discussion (Misc queries) | |||
Compare text string of a cell in Column A VS another cell in Colum | New Users to Excel | |||
Compare text string of a cell in Column A VS another cell in Colum | Excel Discussion (Misc queries) | |||
Compare text string of a cell in Column A VS another cell in Colum | Excel Worksheet Functions | |||
How do I compare cells and if FALSE compare to next cell in EXCEL | Excel Worksheet Functions |