Posted By

joseamirandavelez on 06/09/12


Tagged

split vba


Versions (?)

Who likes this?

1 person have marked this snippet as a favorite

joseamirandavelez


Alternative Split Function


 / Published in: Visual Basic
 

Found in the internet. By default, it splits using all the characters in CHARS. Useful to get a list of words in a string.

  1. Public Function Split(ByVal InputText As String, Optional ByVal Delimiter As String) As Variant
  2.  
  3. ' This function splits the sentence in InputText into
  4. ' words and returns a string array of the words. Each
  5. ' element of the array contains one word.
  6.  
  7. ' This constant contains punctuation and characters
  8. ' that should be filtered from the input string.
  9. Const CHARS = ".!?,;:""'()[]{}|="
  10. Dim strReplacedText As String
  11. Dim intIndex As Integer
  12.  
  13. ' Replace tab characters with space characters.
  14. strReplacedText = Trim(Replace(InputText, _
  15. vbTab, " "))
  16.  
  17. ' Filter all specified characters from the string.
  18. For intIndex = 1 To Len(CHARS)
  19. strReplacedText = Trim(Replace(strReplacedText, _
  20. Mid(CHARS, intIndex, 1), " "))
  21. Next intIndex
  22.  
  23. ' Loop until all consecutive space characters are
  24. ' replaced by a single space character.
  25. Do While InStr(strReplacedText, " ")
  26. strReplacedText = Replace(strReplacedText, _
  27. " ", " ")
  28. Loop
  29.  
  30. ' Split the sentence into an array of words and return
  31. ' the array. If a delimiter is specified, use it.
  32. 'MsgBox "String:" & strReplacedText
  33. If Len(Delimiter) = 0 Then
  34. Split = VBA.Split(strReplacedText)
  35. Else
  36. Split = VBA.Split(strReplacedText, Delimiter)
  37. End If
  38. End Function

Report this snippet  

You need to login to post a comment.