Results 1 to 4 of 4
  1. #1
    MsAxes is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2021
    Posts
    100

    How to remove last characters of a string?

    Hi all - I have a column where the length varies. It starts and ends with alpha characters with numbers in between. I would like to remove the last alpha characters (can be 1-4). I found the code below that removes all the alpha characters. Does anyone know how to tweak it so it only removes the last alpha characters only?

    Code:
    Public Function RemoveAlpha(ByVal strText As String) As String
    
        Dim strTemp As String
        Dim intCounter As Integer
    
    
        For intCounter = 1 To Len(strText)
            If IsNumeric(Mid(strText, intCounter, 1)) Then
                strTemp = strTemp & Mid(strText, intCounter, 1)
            End If
        Next intCounter
    
    
        RemoveAlpha = strTemp
    
    
    End Function


  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Start at the end of the string with a loop and inspect the last 4 characters.
    Or start at len - 4, and stop when you find a non numeric char.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Code:
    Public Function RemoveAlpha(ByVal strText As String) As String
    Dim intCounter As Integer
    
        For intCounter = Len(strText) To 1 Step -1
    
            If IsNumeric(Mid(strText, intCounter, 1)) Then ' a number found so assign everything <=intcounter
    
               RemoveAlpha = Left(strText, intCounter)
               Exit Function
    
            End If
    
        Next intCounter
    
    End Function

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You could use InStrRev to loop backwards and remove any non-numeric characters or use what you have in conjunction with Left and Instr on the original to build the left side characters:

    Left([YourField],Instr([YourField],RemoveAlpha([YourField])) & RemoveAlpha([YourField])

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

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

Similar Threads

  1. Replies: 1
    Last Post: 07-22-2015, 08:11 AM
  2. Replies: 7
    Last Post: 04-14-2015, 03:26 PM
  3. Replies: 12
    Last Post: 03-03-2013, 07:13 PM
  4. Replies: 5
    Last Post: 06-28-2012, 10:49 AM
  5. Replies: 5
    Last Post: 03-10-2011, 02:19 PM

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