Results 1 to 3 of 3
  1. #1
    gameemaster is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2016
    Posts
    23

    Trim Data by Removing Letters From Numbers

    Hello,



    I have a large amount of data I am importing into Access. Im importing two separate spreadsheets and using an inner join query to find matches. However, on one of the imports to Access the data is ridden with letters on the left and right side of the numbers and it is throwing off my inner join match query. Below is an example of what is being imported.

    105273R = 6 digits + Letter
    105281RR = 6 digits + 2 Letters
    119251A = 6 digits + Letter
    95226BR = 5 digits + 2 Letters
    97619R = 5 digits + 1 Letter

    As you can see, some of the numbers are 5 digits or 6 digits long, but they have letters at the end. I'm looking for either a query expression, or probably an update query to remove the letters regardless if the number is 5 or 6 digits long -- I simply want to remove the letter(s). I want this to be done in Access, and not in Excel, prior to the import.

    Can you anybody help me with removing these letters from the numbers that have been imported into a table in Access? Thank so much.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    If the numbers are first, the Val() function should strip the letters. If the letters could be first, you'll probably need a custom function that examines the string character by character.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    jwhite is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Dec 2012
    Location
    North Carolina
    Posts
    349
    Perhaps add a new field to the temp table with the errant letters called something else. Then run a function to update that field on the Val() of the applicable field. Then have your join on the new field.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. mix of letters and numbers on the form
    By iscinar in forum Forms
    Replies: 15
    Last Post: 10-20-2013, 06:11 AM
  2. Data Validation for letters and numbers
    By bassplayer79 in forum Programming
    Replies: 5
    Last Post: 09-16-2013, 03:19 PM
  3. Replies: 1
    Last Post: 04-16-2013, 07:57 PM
  4. IIF with numbers and letters
    By hzrdc2 in forum Queries
    Replies: 2
    Last Post: 03-28-2013, 07:29 AM
  5. Removing all letters or all numbers from string
    By Hayley_sql in forum Programming
    Replies: 2
    Last Post: 09-16-2009, 02:01 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums