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

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

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

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

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

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

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

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

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

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