Hello,

I have a table with a field for county. The data in this [County_Template] field needs to be cleaned up, but since I want to keep the original names, I created a new field called [County_Clean]. In this field, I want to delete the word county and delete anything that is in parentheses.

For example: Barbour County (AL) >> Barbour

How do I go about doing this?
Thanks,
Caroline

Getting rid of 'county' is simple...

Update tablename set [County_clean] = replace([County_Template],"County", "")

But the brackets is a different kettle of fish unless you can say that the brackets are always at the end of the text?

Thanks.
And yes, luckily they're at the end, and it's always (XX) >> state abbreviation.

For some reason though the replace function is returning a blank field...

UPDATE tbl1004 SET [County_Clean] = Replace([County_Template],"County","");

That doesn't really make much sense to me.

[County_template] isn't a lookup field by any chance is it?


Getting rid of the brackets...(but this will have to wait until you have the first bit sorted out)

Update tbl1004 set County_clean = left(county_clean, instrrev(county_clean, "(")-1)

No, not a look up field.

Try the following in the immediate window..

?replace("aaaaa county xxx (vv)", "county", "")

You should get...

aaaaa xxx (vv)

Then just do a query..

Select replace("aaaaa county xxx (vv)", "county", "") as Test, [County_template] from tbl1004

and check that replace works ok in the query.

when you created the new field it is empty?
because this is ultimately a one time exercise, why not copy the original field to the new field, then do the update with the replace function ?

Thank you both. I have been given a new set of data that is helping me along, so I apologize, but I will not need this anymore for this specific project. But I will keep this code in mind for future reference! Thanks again!