bryVA
05-22-2009, 07:55 PM
Hello,
I am trying to take this string off of the clipboard
Lou Pole
123 Fake Street
Anytown, AS 12345
There are carriage returns and I want to breakup the string so I have three variables 1 with the Name, 2 with the street address and 3 the city, state and zip.
I have the following code so far but can't figure out how to manipulate the string.
Dim DataObj As New MSForms.DataObject
Dim S As String
DataObj.GetFromClipboard
S = DataObj.GetText
I tried to use the following code which I got from Ozgrid but it seemed to only extract the street address and not the name or City, state, zip.
Option Compare Text
Public Function Get_Word(text_string As String, nth_word) As String
Dim lWordCount As Long
With Application.WorksheetFunction
lWordCount = Len(text_string) - Len(.Substitute(text_string, " ", "")) + 1
If IsNumeric(nth_word) Then
nth_word = nth_word - 1
Get_Word = Mid(Mid(Mid(.Substitute(text_string, vbCrLf, "^", nth_word), 1, 256), _
.Find("^", .Substitute(text_string, vbCrLf, "^", nth_word)), 256), 2, _
.Find(vbCrLf, Mid(Mid(.Substitute(text_string, vbCrLf, "^", nth_word), 1, 256), _
.Find("^", .Substitute(text_string, vbCrLf, "^", nth_word)), 256)) - 2)
ElseIf nth_word = "First" Then
Get_Word = Left(text_string, .Find(vbCrLf, text_string) - 1)
ElseIf nth_word = "Last" Then
Get_Word = Mid(.Substitute(text_string, vbCrLf, "^", Len(text_string) - _
Len(.Substitute(text_string, vbCrLf, ""))), .Find("^", .Substitute(text_string, vbCrLf, "^", _
Len(text_string) - Len(.Substitute(text_string, vbCrLf, "")))) + 1, 256)
End If
End With
End Function
Can any one help me with this?
Thanks for all you help guys.
I am trying to take this string off of the clipboard
Lou Pole
123 Fake Street
Anytown, AS 12345
There are carriage returns and I want to breakup the string so I have three variables 1 with the Name, 2 with the street address and 3 the city, state and zip.
I have the following code so far but can't figure out how to manipulate the string.
Dim DataObj As New MSForms.DataObject
Dim S As String
DataObj.GetFromClipboard
S = DataObj.GetText
I tried to use the following code which I got from Ozgrid but it seemed to only extract the street address and not the name or City, state, zip.
Option Compare Text
Public Function Get_Word(text_string As String, nth_word) As String
Dim lWordCount As Long
With Application.WorksheetFunction
lWordCount = Len(text_string) - Len(.Substitute(text_string, " ", "")) + 1
If IsNumeric(nth_word) Then
nth_word = nth_word - 1
Get_Word = Mid(Mid(Mid(.Substitute(text_string, vbCrLf, "^", nth_word), 1, 256), _
.Find("^", .Substitute(text_string, vbCrLf, "^", nth_word)), 256), 2, _
.Find(vbCrLf, Mid(Mid(.Substitute(text_string, vbCrLf, "^", nth_word), 1, 256), _
.Find("^", .Substitute(text_string, vbCrLf, "^", nth_word)), 256)) - 2)
ElseIf nth_word = "First" Then
Get_Word = Left(text_string, .Find(vbCrLf, text_string) - 1)
ElseIf nth_word = "Last" Then
Get_Word = Mid(.Substitute(text_string, vbCrLf, "^", Len(text_string) - _
Len(.Substitute(text_string, vbCrLf, ""))), .Find("^", .Substitute(text_string, vbCrLf, "^", _
Len(text_string) - Len(.Substitute(text_string, vbCrLf, "")))) + 1, 256)
End If
End With
End Function
Can any one help me with this?
Thanks for all you help guys.