How to Get Rid Of Empty Cell In String Array RRS feed

  • Question

  • Let's say I have the following string: Hi   Hi

    Function Help(Source As String)
    Dim arr As String
    arr=Split(Source, " ")
    'Source is Hi   Hi, so arr is a 1 x 3 array with the second element being a space

    I want to get rid of any elements that are spaces.  How would I do so?

    Thursday, December 3, 2015 5:19 AM

All replies

  • You can use regular expressions to do it or use Replace.  Here is a Replace solution.  You could have any number of extra spaces so you need to loop until all extra spaces are removed.

    Function RemoveExtraSpaces(s As String) As String
      Dim done As Boolean: done = False
      Dim sBefore As String
      Dim sAfter As String
      sBefore = s
      While Not done
        sAfter = Replace(sBefore, "  ", " ")
        If sAfter = sBefore Then
          RemoveExtraSpaces = sAfter
          Exit Function
          sBefore = sAfter
        End If
    End Function
    Sub Test()
      Dim s As String
      s = RemoveExtraSpaces("Hi  Hi")
      MsgBox (s)
      s = RemoveExtraSpaces("Hi                                  Hi")
      MsgBox (s)
    End Sub

    Friday, December 4, 2015 1:28 PM