PDA

View Full Version : Solved: Manipulate String based on Carriage Return



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.

mikerickson
05-22-2009, 10:21 PM
Dim DataObj As New MSForms.DataObject
Dim S As String
Dim NameAddressCity as Variant

DataObj.GetFromClipboard
S = DataObj.GetText

NameAddressCity = Split(S, vbCr)
MsgBox NameAddressCity(0) & " is the Name"
MsgBox NameAddressCity(1) & " is the Address"
MsgBox NameAddressCity(2) & " is the City, St. zip"

macropod
05-22-2009, 10:24 PM
Hi bryva,

Why not:

Sub Test()
Dim DataObj As New MSForms.DataObject
Dim S As String, i As Integer
DataObj.GetFromClipboard
S = DataObj.GetText
For i = 0 To UBound(Split(S, vbCr))
MsgBox Split(S, vbCr)(i)
Next
End Sub

bryVA
05-23-2009, 08:26 AM
Thanks a bunch Macropod and mikerickson. That is exactly what I needed. You guys are amazing. Macropod you are always helping me, a big thanks to you for your willingness to share your expert knowledge.

mdmackillop
05-24-2009, 08:44 AM
Hi bryVA
Please remember to mark your threads Solved using the Thread Tools dropdown.
Regards
MD

bryVA
05-25-2009, 12:47 PM
I must be blind but when I click on the thread tools it does not give me an option to Solve the thread. Am I blind?

Thank you for all your help,

mdmackillop
05-25-2009, 12:51 PM
You don't see this? What browser are you using? I have noticed differences.

bryVA
05-25-2009, 08:12 PM
I am using Chrome. If doesn't give me that drop down at all. It doesn't give the triangle at all. The only thing I can do is click on it and it takes me to the bottom of the page which shows Show Printable version, email this page and unsubscribe from this thread.

I will use IE next time to solve the thread.

Thanks

mdmackillop
05-26-2009, 12:07 AM
I tried Chrome briefly and found a few web page controls were missing.