Advanced Search

Extract text from a String

/ Published in: Visual Basic
Save to your folder(s)
Two functions that can extract text from a string. Must have the Option Compare Text at the top of the module.

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. ​
  2.  
  3. Option Compare Text
  4. Function Get_Word(text_string As String, nth_word) As String
  5. Dim lWordCount As Long
  6.         With Application.WorksheetFunction
  7.         lWordCount = Len(text_string) - Len(.Substitute(text_string, " ", "")) + 1
  8.         If IsNumeric(nth_word) Then
  9.            nth_word = nth_word - 1
  10.             Get_Word = Mid(Mid(Mid(.Substitute(text_string, " ", "^", nth_word), 1, 256), _
  11.                 .Find("^", .Substitute(text_string, " ", "^", nth_word)), 256), 2, _
  12.                 .Find(" ", Mid(Mid(.Substitute(text_string, " ", "^", nth_word), 1, 256), _
  13.                 .Find("^", .Substitute(text_string, " ", "^", nth_word)), 256)) - 2)
  14.         ElseIf nth_word = "First" Then
  15.             Get_Word = Left(text_string, .Find(" ", text_string) - 1)
  16.         ElseIf nth_word = "Last" Then
  17.             Get_Word = Mid(.Substitute(text_string, " ", "^", Len(text_string) - _
  18.             Len(.Substitute(text_string, " ", ""))), .Find("^", .Substitute(text_string, " ", "^", _
  19.             Len(text_string) - Len(.Substitute(text_string, " ", "")))) + 1, 256)
  20.         End If
  21.     End With
  22. End Function
  23.  
  24. Function ExtractElement(Txt, n, Separator) As String
  25. '   Returns the nth element of a text string, where the elements
  26. '   are separated by a specified separator character
  27.     Dim Txt1 As String, TempElement As String
  28.     Dim ElementCount As Integer, i As Integer
  29.  
  30.     Txt1 = Txt
  31. '   If space separator, remove excess spaces
  32.     If Separator = Chr(32) Then Txt1 = Application.Trim(Txt1)
  33.  
  34. '   Add a separator to the end of the string (if necessary)
  35.     If Right(Txt1, 1) <> Separator Then Txt1 = Txt1 & Separator
  36.  
  37. '   Initialize
  38.     ElementCount = 0
  39.     TempElement = ""
  40.  
  41. '   Extract each element
  42.     For i = 1 To Len(Txt1)
  43.         If Mid(Txt1, i, 1) = Separator Then
  44.             ElementCount = ElementCount + 1
  45.             If ElementCount = n Then
  46. '               Found it, so exit
  47.                 ExtractElement = TempElement
  48.                 Exit Function
  49.             Else
  50.                 TempElement = ""
  51.             End If
  52.         Else
  53.             TempElement = TempElement & Mid(Txt1, i, 1)
  54.         End If
  55.     Next i
  56.     ExtractElement = ""
  57. End Function

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.