WEB Database Guru - Remove Blank Spaces From String

Discussion in 'OT Technology' started by LyFeWoRx, Dec 16, 2008.

  1. LyFeWoRx

    LyFeWoRx 2 + 2 = 4 OT Supporter

    Joined:
    Jul 17, 2006
    Messages:
    120
    Likes Received:
    0
    Location:
    New York, New York
    Is there a command to remove blank spaces from the center of a string?

    I know there are a couple ways to Ltrim and Rtrim but what about the center?

    Example: Jaba Jaba
    Should Be: JabaJaba

    Thanks in advance!

    I'm working in Microsoft Access right now. :mamoru:
     
  2. LyFeWoRx

    LyFeWoRx 2 + 2 = 4 OT Supporter

    Joined:
    Jul 17, 2006
    Messages:
    120
    Likes Received:
    0
    Location:
    New York, New York
    Code:
    'Function removes spaces in front, back and middle of string
    Public Function trimSpaces(strInput)
        Dim strCharRemove As String                                 'assigns the char to look for & replace
        Dim strCurChar As String                                    'Current character to match up
        Dim i As Integer                                            'count of chars in string
        Dim lngSpaceCount As Long                                   'tracks the count of spaces
        strCharRemove = " "                                         'looks for a space (can be any character)
        
        'trim spaces between text
        For i = 1 To Len(strInput)
            strCurChar = Mid(strInput, i, 1)                        'get the character count of string
            
            If InStr(strCharRemove, strCurChar) = 0 Then            'if the character matches a space
                trimSpaces = trimSpaces & strCurChar                'create parsed string
                lngSpaceCount = 0                                   'reset to zero
            Else
                If lngSpaceCount < 1 Then                           'if the space count is already 1, do nothing
                    trimSpaces = trimSpaces & strCurChar            'else create parsed string
                    lngSpaceCount = lngSpaceCount + 1               'add 1 to the space count so the space is not repeated
                Else
                    lngSpaceCount = lngSpaceCount + 1               'track count of spaces
                End If
            End If
        Next i
        
        'trim spaces in front & in back of text
        trimSpaces = Trim(trimSpaces)
    End Function
    I want to make this script remove all spaces, right now it changes all spaces to 1 space.
     
  3. LyFeWoRx

    LyFeWoRx 2 + 2 = 4 OT Supporter

    Joined:
    Jul 17, 2006
    Messages:
    120
    Likes Received:
    0
    Location:
    New York, New York
    or would it be easier to modify this script?

    Code:
    Function OneSpace(pstr As String) As String
    
    '*******************************************
    'Purpose:   Removes excess spaces from a string
    'Input:     ? onespace(" now    is  the  time for   all good men  ")
    'Output:    "now is the time for all good men"
    '*******************************************
    
    Dim strHold As String
        strHold = Trim(pstr)
        Do While InStr(strHold, "  ") > 0
          strHold = Left(strHold, InStr(strHold, "  ") - 1) & Mid(strHold, InStr(strHold, "  ") + 1)
        Loop
        OneSpace = strHold
        
    End Function
     
  4. LyFeWoRx

    LyFeWoRx 2 + 2 = 4 OT Supporter

    Joined:
    Jul 17, 2006
    Messages:
    120
    Likes Received:
    0
    Location:
    New York, New York
    NVM... I'm a dumbass.

    Replace Function is your friend. Replace([FieldName]," ","")

    I tried it earlier, but I must have mistyped the code or field.

    Ignore this post. =(
     
  5. Ricky

    Ricky █▄ █▄█ █▄ ▀█▄

    Joined:
    Jun 17, 2005
    Messages:
    38,766
    Likes Received:
    6
    Don't delete the thread. Might come in handy for someone one day :o
     

Share This Page