Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Eliminating excess characters in Excel import (Access 2003 SP1 Office

    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.

  3. #3
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Eliminating excess characters in Excel import (Access 2003 SP1 Office

    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?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Eliminating excess characters in Excel import (Access 2003 SP1 Office

    It took you 3 months to find out? <img src=/S/yikes.gif border=0 alt=yikes width=15 height=15>

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

  5. #5
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Eliminating excess characters in Excel import (Access 2003 SP1 Office

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

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

    Thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •