Posted By

BDOGG32 on 04/01/15


Tagged

String excel extract vba


Versions (?)

Who likes this?

2 people have marked this snippet as a favorite

BDOGG32
steveulven


Extract text from a String


 / Published in: Visual Basic
 

Two functions that can extract text from a string. Must have the Option Compare Text at the top of the module.

  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  

You need to login to post a comment.