Eliminating excess characters in Excel import (Access 2003 SP1 Office XP)

Topic

New Reply

Welcome back!

I have a work sheet in Excel which includes a Part No field, imported from an external database server. The field is a max of 20 characters long. The problem is that the field has been filled with *’s. For example, the Part No 123456 has been completed to 123456**************. The numerical part is anywhere from 5 to 15 characters long. How can I get rid of the *’s? This can be done either in the Excel sheet, before importing to Access, or in Access after importing.

Reply | Quote

Replies

In Excel, you can remove the asterisks as follows:
– Select the Part No column.
– Select Edit | Replace.
– Enter ~* in the Find What box (the ~ indicates that the * is to be used as a literal character, not as a wildcard).
– Leave the Replace With box blank.
– Click Replace All.
If the result is truly numeric, the advantage is that the column will be imported as numeric in Access.

Reply | Quote

Everything OK up to now, but just ran accross a problem. If there is a formula which includes an *, that will be “found”, along with the Part Nos. Any way that I can overcome that problem?

Reply | Quote

It took you 3 months to find out?

See my previous reply: select only the Part No column. Excel will find/replace within the selected column.

Reply | Quote

The problem just came up. Things have been fine up till now.

Selecting only that column restricts the search, and solves the problem.

Thanks.

Reply | Quote