Compare One Cell to the First 10 Characters of Another Cell

[Jill]

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.

 

[Gary''s Student]

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

 

[Ron Rosenfeld]

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

 

[Jill]

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

 

[Ron Rosenfeld]

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

 

[Jill]

WOO HOO -- YES!

Thank you so much!!!
--
Jill

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

 

[Ron Rosenfeld]

WOO HOO -- YES!

Thank you so much!!!

You're welcome. Glad to help. Thanks for the feedback.
--ron

 

[T. Valko]

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)

 

[Frank Sabella]

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

 

[Ron Rosenfeld]

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