---------------------------
SAMPLE SHEET
---------------------------
ORGANIZATION | ADDRESS |
Franklin County DJFS |123 E. Main St. Columbus,OH|
Madison County DJFS |123 S. Lucas St. London, OH|
Guernsey County DJFS |4442 E 33rd St. Blahblah,OH|

I am trying to assign each entry to 1 of 8 regions based
on the county named in COL A. Is there a way (I tried
using [IF(FIND(OR("text","text"),A2)=1,"...] to search
for one of multiple strings within a string? For
example 'Franklin' and 'Madison' are both assigned to
the 'Central Ohio RTC' and 'Guernsey' is 'East Central
RTC', etc.

Any assistance would be GREATLY appreciated.

Thanks,
Chris

There are 88 total counties, which was causing a 'formula
too long' error in some trials.

Thanks,
Chris

i assume that you have a list of all 88 counties? Have
the next column with the number that you want to
assosciate with it, then rangename the table ( both
columns) with a name like 'Counties'

Next, on your address, add a column to get the county name
eg
=LEFT(A1,FIND(" ",A1)-1)
then add this in the next column (or combine them)
=VLOOKUP(C1,Counties,2,False)
where C1 is the stripped out county


Patrick Molloy
Microsoft Excel MVP

Send me your workbook if you want me to demo it

-----Original Message-----
There are 88 total counties, which was causing
a 'formula
too long' error in some trials.

Thanks,
Chris

.

Might not be suitable, but making a lookup table saves all kinds of
problems like this (???)


---
Message posted from http://www.ExcelForum.com/

On Tue, 9 Dec 2003 21:49:00 -0800, "Chris Bruno" wrote:

---------------------------
SAMPLE SHEET
---------------------------
ORGANIZATION | ADDRESS |
Franklin County DJFS |123 E. Main St. Columbus,OH|
Madison County DJFS |123 S. Lucas St. London, OH|
Guernsey County DJFS |4442 E 33rd St. Blahblah,OH|

I am trying to assign each entry to 1 of 8 regions based
on the county named in COL A. Is there a way (I tried
using [IF(FIND(OR("text","text"),A2)=1,"...] to search
for one of multiple strings within a string? For
example 'Franklin' and 'Madison' are both assigned to
the 'Central Ohio RTC' and 'Guernsey' is 'East Central
RTC', etc.

Any assistance would be GREATLY appreciated.

Thanks,
Chris

Set up a table matching Counties and regions:

County Region
Franklin Central Ohio RTC
Madison Central Ohio RTC
Guernsey East Central RTC

Name the table RegionTable

Now, I am assuming you always enter the ORGANIZATION with the name of the
County first, followed by a <space.

This formula will return the region:

=VLOOKUP(LEFT(A2,FIND(" ",A2)-1),RegionTable,2,FALSE)



--ron