sorting on embedded characters

R

rhizopoda

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

This is a hard to describe! I have a 54,000-row Excel file, listing all National Institutes of Health (NIH) grants for 2008. I need to extract just the ones funded through the National Cancer Institute (NCI). Unfortunately, that information is buried in the "Project Number" column. The fourth and fifth characters in that column indicate the funding agency (CA = NCI funding), but the characters in the first through third positions should be ignored for this purpose. A sample listing of five grants: R13RR023231, R44CA128139, K25DA021172, U01AI069436, R01CA129514. Is there a way to sort the whole file on characters four through eleven of one column?
 
B

Bob Greenblatt

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

This is a hard to describe! I have a 54,000-row Excel file, listing all
National Institutes of Health (NIH) grants for 2008. I need to extract just
the ones funded through the National Cancer Institute (NCI). Unfortunately,
that information is buried in the "Project Number" column. The fourth and
fifth characters in that column indicate the funding agency (CA = NCI
funding), but the characters in the first through third positions should be
ignored for this purpose. A sample listing of five grants: R13RR023231,
R44CA128139, K25DA021172, U01AI069436, R01CA129514. Is there a way to sort the
whole file on characters four through eleven of one column?
Click to expand...
Your best bet is to create another column for funding source. Use a formula
like=mid(a1,4,2) to extract the funding code. Fill this down as necessary.
Then sort on this column.
 
R

rhizopoda

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

This is a hard to describe! I have a 54,000-row Excel file, listing all
National Institutes of Health (NIH) grants for 2008. I need to extract just
the ones funded through the National Cancer Institute (NCI). Unfortunately,
that information is buried in the "Project Number" column. The fourth and
fifth characters in that column indicate the funding agency (CA = NCI
funding), but the characters in the first through third positions should be
ignored for this purpose. A sample listing of five grants: R13RR023231,
R44CA128139, K25DA021172, U01AI069436, R01CA129514. Is there a way to sort the
whole file on characters four through eleven of one column?
Click to expand...
Your best bet is to create another column for funding source. Use a formula
like=mid(a1,4,2) to extract the funding code. Fill this down as necessary.
Then sort on this column.

--
Bob Greenblatt [MVP], Macintosh
bobgreenblattATmsnDOTcom

Thank you so much! I've only used Excel for fairly basic tasks, and didn't know about the Mid function. That's a great help.[/QUOTE]
 
Top