Hi Mick,
Seeing how you mentioned that you were using Excel to do your list. I figured i ask my gf for advice, she is an expert with Excel and its functions :)
I would think access would have a similar feature like excel has (FIND and REPLACE). I have tested this in Excel 2000. And it works.
Here is what she suggests:
select all rows and columns in the access table you wish to do the replace. I know the you only want to do it on one field, but this will work too. ( I also believe you can do it with just one column, but I don't want the integrity of the data to get screwed up ) Copy the data in the table. Open up Excel, and paste the data into a work sheet. Once you are in Excel, select the character you want to replace and copy that into the clipboard. Then select (highlight) the column that you wish to do the replace in. Press ctrl-f to bring the find and replace and paste the selected character into the find field. Then select the replace option and enter the value you wish to replace it with. This should do a find and replace , for all values in the column you had selected. Go back to access and copy the table, just the structure. In Excel hightlight all the data w/o the column headers and copy. Then open the new table in Access and select the empty row and paste the data.
I hope the explanation is clear.
Let me know how it goes.
Seeing how you mentioned that you were using Excel to do your list. I figured i ask my gf for advice, she is an expert with Excel and its functions :)
I would think access would have a similar feature like excel has (FIND and REPLACE). I have tested this in Excel 2000. And it works.
Here is what she suggests:
select all rows and columns in the access table you wish to do the replace. I know the you only want to do it on one field, but this will work too. ( I also believe you can do it with just one column, but I don't want the integrity of the data to get screwed up ) Copy the data in the table. Open up Excel, and paste the data into a work sheet. Once you are in Excel, select the character you want to replace and copy that into the clipboard. Then select (highlight) the column that you wish to do the replace in. Press ctrl-f to bring the find and replace and paste the selected character into the find field. Then select the replace option and enter the value you wish to replace it with. This should do a find and replace , for all values in the column you had selected. Go back to access and copy the table, just the structure. In Excel hightlight all the data w/o the column headers and copy. Then open the new table in Access and select the empty row and paste the data.
I hope the explanation is clear.
Let me know how it goes.