Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    jharding08 is offline Novice
    Windows 10 Office 365
    Join Date
    Feb 2021
    Posts
    6

    Post Code to strip off characters at end of string

    I am doing an EDI file and have strings that are delimited by "*"s.


    Right now, I create the string with blanks if the data does not exist, so it could look like:


    SV1*HC:90686*33*UN*1****************~


    The "~" is the end of line character so I need to leave that, but I need to strip all the "*"s off until I get to a character other than "*"


    It should look like


    SV1*HC:90236:::::*33*UN*1~


    Same for if letter at end:
    DMG*D8*P*******~


    Should be:
    DMG*D8*P~


    Best way to do this?

  2. #2
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Replace() Function is your best bet, or a regular expression.

    It would be easier if you didn't add the * for the blanks? Do you do that?
    This is made more awkward by the fact that * is a wildcard character in Access - can you change it to something else? | or ¬ ?

    Search for "**" and replace with something else would be my starting point
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    jharding08 is offline Novice
    Windows 10 Office 365
    Join Date
    Feb 2021
    Posts
    6
    Problem is that if there are no values and its just ****, then there is a value, like "SV1*HC:90686*33*UN*1****P*******", then I need to strip after the P, but keep the *s between 1 and P. I think I need to work Right to Left and find first char not *, then get that position and strip the rest to the right. Is there a INSTR function that works right to left?

  4. #4
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  5. #5
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    My thought is to loop from right to left, starting with position 2, removing anything that is * and stopping when it hits a character that is not *.
    However, the requirement is not crystal clear as you introduce a bunch of colons that were not in the original value but are in the desired result. So my suggestion disregards that.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664

    And some numbers changed?
    Code:
    SV1*HC:90686*33*UN*1****************~
    
    It should look like
    SV1*HC:90236:::::*33*UN*1~
    Hmmmmm.........

  7. #7
    jharding08 is offline Novice
    Windows 10 Office 365
    Join Date
    Feb 2021
    Posts
    6
    This is what I came up with:


    Code:
    Public Function StripString(strString As String, delimiter As String) As String
        Dim strMain, strEnd As String
        Dim intLastChar As Integer
        Dim strStripped As String
        Dim i As Integer
        
        strEnd = Right(strString, 1)
        If strEnd = "~" Then
            strMain = Left(strString, Len(strString) - 1)
        Else
            strMain = strString
        End If
    
    
        For i = Len(strMain) To 1 Step -1
        
            If Mid(strMain, i, 1) <> delimiter Then
                strStripped = Left(strMain, i)
                Exit For
            Else
                strStripped = ""
            End If
            
        Next i
        
        If strEnd = "~" Then
            StripString = strStripped & strEnd
        Else
            StripString = strStripped
        End If
    End Function

  8. #8
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I have set up a small function.

    Code:
    Function removeTrailingChars(sTerm As String) As String
        Dim i As Integer               'counter
        Dim suffix As String           'check for "~"
        
        If Right(sTerm, 1) = "~" Then
            suffix = Right(sTerm, 1)
            sTerm = Mid(sTerm, 1, Len(sTerm) - 1)
        End If
        For i = Len(sTerm) To 0 Step -1
            If Mid(sTerm, i, 1) = "*" Then
            'Debug.Print i, Mid(sTerm, i, 1)   'for testing/debug
            Else
                removeTrailingChars = Left(sTerm, i) & IIf(suffix = "~", "~", "")
                Exit For
            End If
        Next
    End Function
    Test routine:
    Code:
    Sub testStars()
        Dim i As Integer
        Dim a(3) As String
        a(0) = "SV1*HC:90686*33*UN*1****P*******"
        a(1) = "SV1*HC:90686*33*UN*1****************~"
        a(2) = "SV1*HC:90686*33*UN*1**CX**********"
        a(3) = "SV1*HC:90686*33*UN*1**cx**********~"
    
        For i = LBound(a) To UBound(a)
            Debug.Print removeTrailingChars(a(i))     'just printing the result to immediate window
        Next i
    End Sub

    OOOooops: I see you have some code.

  9. #9
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Late to the party, but what else am I going to do with it?
    Code:
    Function RemAsterisk(strIn As String) As String
    Dim intEnd As Integer, i As Integer
    
    intEnd = Len(strIn) - 1
    For i = 1 To intEnd
       If Mid(strIn, intEnd - i, 1) <> "*" Then Exit For
    Next
    RemAsterisk = Left(strIn, Len(strIn) - i - 1) & Right(strIn, 1)
    End Function
    Doesn't pay to stop for dinner!

    EDIT - it seems I could not resist fixing it in spite of my comment (removed)
    input/output
    ?remasterisk ("SV1*HC:90686*33*UN*1****************~")

    SV1*HC:90686*33*UN*1~
    Last edited by Micron; 03-03-2021 at 07:05 PM. Reason: added info
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  10. #10
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664

    The More You Know...

    @jharding08,
    In the code you wrote, you have this line
    Code:
    Dim strMain, strEnd As String
    In this line, you have declared "strMain" as a Variant and "strEnd" as a String.

    In VBA, you MUST explicitly declare the variable type. If you don't, the variable is automatically a Variant be default.


    You can use either of the following:
    Code:
    Dim strMain As String, strEnd As String
    Code:
    Dim strMain As String
    Dim strEnd As String
    I typically declare each variable on a separate line. It's easier for me to find specific variables and I group types together; all Strings, all Integers, all Longs......

  11. #11
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    What about this?
    Code:
    Replace(Replace("DMG*D8*P*******~","**",""),"*~","~")

  12. #12
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Quote Originally Posted by accesstos View Post
    What about this?
    Code:
    Replace(Replace("DMG*D8*P*******~","**",""),"*~","~")
    Then what do you do when it's this
    SV1*HC:90686*33*UN*1****************~
    or something different yet again?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  13. #13
    jharding08 is offline Novice
    Windows 10 Office 365
    Join Date
    Feb 2021
    Posts
    6
    All great ideas. Thank you for your help

  14. #14
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    Quote Originally Posted by Micron View Post
    Then what do you do when it's this
    or something different yet again?
    Code:
    ?Replace(Replace("SV1*HC:90686*33*UN*1****************~","**",""),"*~","~")
    SV1*HC:90686*33*UN*1~
    ?RemAsterisk("SV1*HC:90686*33*UN*1****************~")
    SV1*HC:90686*33*UN*1~
    ?removeTrailingChars("SV1*HC:90686*33*UN*1****************~")
    SV1*HC:90686*33*UN*1~
    Have I missed something about the returned value or do you mean the literal text value of my example?

  15. #15
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    Quote Originally Posted by jharding08 View Post
    All great ideas. Thank you for your help
    You are welcome!

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 10
    Last Post: 04-08-2020, 09:18 PM
  2. Extract characters from the String
    By soldat452002 in forum Access
    Replies: 12
    Last Post: 10-02-2018, 09:31 PM
  3. Get trailing Characters from String
    By jaykappy in forum Queries
    Replies: 4
    Last Post: 09-03-2014, 09:19 AM
  4. Replies: 1
    Last Post: 05-02-2013, 10:57 AM
  5. function to strip characters
    By Kirsti in forum Queries
    Replies: 2
    Last Post: 11-01-2012, 07:53 PM

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