combine PARSED STRINGS

L

lmv

I am pasting 4 lines of text into a text box from a search in YELLOWPAGES.COM

LastName, FirstName
1234 streetname rd
City, state, 00000-0000
(000-000-0000)

I have 3 parse buttons to fill that text into fields on my form. Here is my
proceedure.
I paste it in my notes field then cut the address through the phone and hit
my parse button
then I paste address through end of line and parse2
paste the rest delete city and state and get the zip code and phone on the
same
line then parse3 (I don't try to use city and state because they are a
different table and they are numbers not text in the fields on the table this
form is based on)

The following parse buttons work independently but I need to COMBINE them
and I don't know how. I do NOT KNOW if the code for the end line is vbCrLf or
?? and I don't know how to find out. So I understand I may have to try
different options but my problem is I don't know how to combine the whole
thing together to even begin.

Can someone help?

Private Sub cmdParse_Click()
On Error GoTo Err_cmdParse_Click

Dim strName As String
Dim strFName As String
Dim strLName As String
Dim intLoc As Integer

Me.Notes.SetFocus
If Me.Notes.Text <> "" Then
strName = Me.Notes.Text
intLoc = InStr(1, strName, " ")
strLName = Left(strName, intLoc - 2)
strFName = Right(strName, Len(strName) - intLoc)
Me.FirstName.SetFocus
Me.FirstName.Text = strFName
Me.LastName.SetFocus
Me.LastName.Text = strLName

End If

Me.Notes = "" 'clears the notes field

Exit_cmdParse_Click:
Exit Sub

Err_cmdParse_Click:
MsgBox Err.Description
Resume Exit_cmdParse_Click

End Sub
'-------------------------------------------------------------------
' cmdParse2_Click()
On Error GoTo Err_cmdParse2_Click

Dim strAddress As String
Dim strFAddress As String
Dim strLAddress As String
Dim intLoc As Integer

Me.Notes.SetFocus
If Me.Notes.Text <> "" Then
strAddress = Me.Notes.Text
intLoc = InStr(1, strAddress, " ")
strLAddress = Left(strAddress, intLoc)
strFAddress = Right(strAddress, Len(strAddress) - intLoc)
Me.streetnumber.SetFocus
Me.streetnumber.Text = strLAddress
Me.StreetName.SetFocus
Me.StreetName.Text = strFAddress
End If
Me.Notes = ""
' --------------rest of parse2 code omited for post
'---------------cmdParse3_Click()
On Error GoTo Err_cmdParse3_Click

Dim strFInfo As String
Dim strPhoneNumber As String
Dim strRPhoneNumber As String
Dim strzipcode As String
Dim strLzipcode As String
Dim intLoc As Integer

Me.Notes.SetFocus
If Me.Notes.Text <> "" Then
strFInfo = Me.Notes.Text
intLoc = InStr(1, strFInfo, " ")
strLzipcode = Left(strFInfo, intLoc)
strRPhoneNumber = Right(strFInfo, Len(strFInfo) - intLoc)
Me.ZipCode.SetFocus
Me.ZipCode.Text = strLzipcode
Me.PhoneNumber.SetFocus
Me.PhoneNumber.Text = strRPhoneNumber
End If
Me.Notes = ""
--------------rest of parse3 code omited for post
 
G

Graham Mandeno

You will need to do some experimenting here to find out what the line
separator is.

First, add this code to the beginning of your cmdParse_Click procedure:

Dim sText as string, sMsg as string, i as integer, sChar as string, iCode as
integer
sText = Me.Notes
For i = 1 to Len(sText)
sChar = Mid(sText, i, 1)
iCode = Asc(sChar)
If iCode < 32 then
sMsg = sMsg & "{" & iCode & "}"
Else
sMsg = sMsg & sChar
End If
Next
MsgBox sMsg
Exit Sub

(Note that you do not need to do a SetFocus to a textbox to reference or
change its value. You just use the default .Value property instead of
..Text)

Now, paste an address and run the code. You will see a MsgBox with the text
from your textbox and some numbers in curly brackets. These numbers are the
ANSI codes for the hidden characters, such as line separators. If the line
separator is vbCrLf then you will see {13}{10}, or you might see {13} (vbCr)
or {10} (vbLf) by itself.

When you know what the separator is, you can then use the Split function to
separate the single string of text into an array of strings, one per line:

Dim aLines() as String, i as integer
aLines = Split(Me.Notes, vbCrLf) ' assuming this is the separator
For i = 0 to UBound(aLines)
Debug.print i, aLines(i)
Next

Note that the array elements will be numbered starting at 0.

Then you can just parse each line as required.
--
Good Luck :)

Graham Mandeno [Access MVP]
Auckland, New Zealand
 
L

lmv

Graham,
Ok I ran the code and it is {13}{10}{13}{10}
On all lines (nothing on the last phone line at the end)
(Note that you do not need to do a SetFocus to a textbox to reference or
change its value. You just use the default .Value property instead of
..Text)
Click to expand...

I am flying by the seat of my pants here... and I don't know what I should
replace /and insert in my code.
When you know what the separator is, you can then use the Split function to
separate the single string of text into an array of strings, one per line:

Dim aLines() as String, i as integer
aLines = Split(Me.Notes, vbCrLf) ' assuming this is the separator
For i = 0 to UBound(aLines)
Debug.print i, aLines(i)
Next

Note that the array elements will be numbered starting at 0.

Then you can just parse each line as required.
Click to expand...

PLeeaassee.. would you mind joining just one of my parse codes to another so
I see how I am suppose to do it?

I don't do this for a living I am just doing it for an application for
myself...

Thank you so much. (I hope!)
 
G

Graham Mandeno

Hi LMV

That is strange... it looks like the line separator is TWO vbCrLfs. Do
blank lines appear in the Notes textbox when you paste the data?

If this really is the case, then the separator for the purposes of the Split
function will be vbCrLf & vbCrLf.

Now, the complexity of the parse code depends on how reliable the format of
the data is. Is it ALWAYS four lines? Is there ALWAYS a comma in the first
line? does the second line ALWAYS start with a number?
PLeeaassee.. would you mind joining just one of my parse codes to another
so
I see how I am suppose to do it?
Click to expand...

Since you asked so nicely.... ;-)

No matter what the answers to these questions, the following code should get
you started:

Private Sub cmdParse_Click()
Dim sText As String, i As Integer, s As String
Dim aLines() As String, sLineSeparator As String
sLineSeparator = vbCrLf & vbCrLf
sText = Me.Notes & ""
aLines = Split(Me.Notes, sLineSeparator)
If UBound(aLines) <> 3 Then
MsgBox "Invalid data - exactly 4 lines expected"
Exit Sub
End If
' Line 1: LastName, FirstName
s = aLines(0)
i = InStr(s, ",")
If i = 0 Then
' no comma - no first name
LastName = s
FirstName = Null
Else
LastName = Trim(Left(s, i - 1))
FirstName = Trim(Mid(s, i + 1))
End If
' Line 2: StreetNumber StreetName
s = aLines(1)
If Val(s) > 0 Then
' might have a street number
i = InStr(s, " ")
If i = 0 Then
' no space - no street number
StreetNumber = Null
StreetName = s
Else
StreetNumber = Trim(Left(s, i - 1))
StreetName = Trim(Mid(s, i + 1))
End If
Else
StreetNumber = Null
StreetName = s
End If
' Line 3: City, State ZipCode
s = aLines(2)
i = InStr(s, ",")
If i = 0 Then
' no comma - put it all in city
City = s
State = Null
ZipCode = Null
Else
City = Trim(Left(s, i - 1))
s = Trim(Mid(s, i + 1))
i = InStr(s, " ")
If i = 0 Then
' no space - assume no zip
State = s
ZipCode = Null
Else
State = Trim(Left(s, i - 1))
ZipCode = Trim(Mid(s, i + 1))
End If
End If
' Line 4: Phone number
PhoneNumber = aLines(3)
End Sub


--
Good Luck :)

Graham Mandeno [Access MVP]
Auckland, New Zealand
 
L

lmv

Haven't worked on it yet but just wanted to say THANK YOU SO MUCH!


Graham Mandeno said:
Hi LMV

That is strange... it looks like the line separator is TWO vbCrLfs. Do
blank lines appear in the Notes textbox when you paste the data?

If this really is the case, then the separator for the purposes of the Split
function will be vbCrLf & vbCrLf.

Now, the complexity of the parse code depends on how reliable the format of
the data is. Is it ALWAYS four lines? Is there ALWAYS a comma in the first
line? does the second line ALWAYS start with a number?
PLeeaassee.. would you mind joining just one of my parse codes to another
so
I see how I am suppose to do it?
Click to expand...

Since you asked so nicely.... ;-)

No matter what the answers to these questions, the following code should get
you started:

Private Sub cmdParse_Click()
Dim sText As String, i As Integer, s As String
Dim aLines() As String, sLineSeparator As String
sLineSeparator = vbCrLf & vbCrLf
sText = Me.Notes & ""
aLines = Split(Me.Notes, sLineSeparator)
If UBound(aLines) <> 3 Then
MsgBox "Invalid data - exactly 4 lines expected"
Exit Sub
End If
' Line 1: LastName, FirstName
s = aLines(0)
i = InStr(s, ",")
If i = 0 Then
' no comma - no first name
LastName = s
FirstName = Null
Else
LastName = Trim(Left(s, i - 1))
FirstName = Trim(Mid(s, i + 1))
End If
' Line 2: StreetNumber StreetName
s = aLines(1)
If Val(s) > 0 Then
' might have a street number
i = InStr(s, " ")
If i = 0 Then
' no space - no street number
StreetNumber = Null
StreetName = s
Else
StreetNumber = Trim(Left(s, i - 1))
StreetName = Trim(Mid(s, i + 1))
End If
Else
StreetNumber = Null
StreetName = s
End If
' Line 3: City, State ZipCode
s = aLines(2)
i = InStr(s, ",")
If i = 0 Then
' no comma - put it all in city
City = s
State = Null
ZipCode = Null
Else
City = Trim(Left(s, i - 1))
s = Trim(Mid(s, i + 1))
i = InStr(s, " ")
If i = 0 Then
' no space - assume no zip
State = s
ZipCode = Null
Else
State = Trim(Left(s, i - 1))
ZipCode = Trim(Mid(s, i + 1))
End If
End If
' Line 4: Phone number
PhoneNumber = aLines(3)
End Sub


--
Good Luck :)

Graham Mandeno [Access MVP]
Auckland, New Zealand



lmv said:
Graham,
Ok I ran the code and it is {13}{10}{13}{10}
On all lines (nothing on the last phone line at the end)


I am flying by the seat of my pants here... and I don't know what I should
replace /and insert in my code.


PLeeaassee.. would you mind joining just one of my parse codes to another
so
I see how I am suppose to do it?

I don't do this for a living I am just doing it for an application for
myself...

Thank you so much. (I hope!)
Click to expand...
Click to expand...
 
G

Graham Mandeno

You're welcome. Let us know how you get on.
--
Good Luck :)

Graham Mandeno [Access MVP]
Auckland, New Zealand

lmv said:
Haven't worked on it yet but just wanted to say THANK YOU SO MUCH!


Graham Mandeno said:
Hi LMV

That is strange... it looks like the line separator is TWO vbCrLfs. Do
blank lines appear in the Notes textbox when you paste the data?

If this really is the case, then the separator for the purposes of the
Split
function will be vbCrLf & vbCrLf.

Now, the complexity of the parse code depends on how reliable the format
of
the data is. Is it ALWAYS four lines? Is there ALWAYS a comma in the
first
line? does the second line ALWAYS start with a number?
PLeeaassee.. would you mind joining just one of my parse codes to
another
so
I see how I am suppose to do it?
Click to expand...

Since you asked so nicely.... ;-)

No matter what the answers to these questions, the following code should
get
you started:

Private Sub cmdParse_Click()
Dim sText As String, i As Integer, s As String
Dim aLines() As String, sLineSeparator As String
sLineSeparator = vbCrLf & vbCrLf
sText = Me.Notes & ""
aLines = Split(Me.Notes, sLineSeparator)
If UBound(aLines) <> 3 Then
MsgBox "Invalid data - exactly 4 lines expected"
Exit Sub
End If
' Line 1: LastName, FirstName
s = aLines(0)
i = InStr(s, ",")
If i = 0 Then
' no comma - no first name
LastName = s
FirstName = Null
Else
LastName = Trim(Left(s, i - 1))
FirstName = Trim(Mid(s, i + 1))
End If
' Line 2: StreetNumber StreetName
s = aLines(1)
If Val(s) > 0 Then
' might have a street number
i = InStr(s, " ")
If i = 0 Then
' no space - no street number
StreetNumber = Null
StreetName = s
Else
StreetNumber = Trim(Left(s, i - 1))
StreetName = Trim(Mid(s, i + 1))
End If
Else
StreetNumber = Null
StreetName = s
End If
' Line 3: City, State ZipCode
s = aLines(2)
i = InStr(s, ",")
If i = 0 Then
' no comma - put it all in city
City = s
State = Null
ZipCode = Null
Else
City = Trim(Left(s, i - 1))
s = Trim(Mid(s, i + 1))
i = InStr(s, " ")
If i = 0 Then
' no space - assume no zip
State = s
ZipCode = Null
Else
State = Trim(Left(s, i - 1))
ZipCode = Trim(Mid(s, i + 1))
End If
End If
' Line 4: Phone number
PhoneNumber = aLines(3)
End Sub


--
Good Luck :)

Graham Mandeno [Access MVP]
Auckland, New Zealand



lmv said:
Graham,
Ok I ran the code and it is {13}{10}{13}{10}
On all lines (nothing on the last phone line at the end)

(Note that you do not need to do a SetFocus to a textbox to reference
or
change its value. You just use the default .Value property instead of
..Text)

I am flying by the seat of my pants here... and I don't know what I
should
replace /and insert in my code.

When you know what the separator is, you can then use the Split
function
to
separate the single string of text into an array of strings, one per
line:

Dim aLines() as String, i as integer
aLines = Split(Me.Notes, vbCrLf) ' assuming this is the separator
For i = 0 to UBound(aLines)
Debug.print i, aLines(i)
Next

Note that the array elements will be numbered starting at 0.

Then you can just parse each line as required.

PLeeaassee.. would you mind joining just one of my parse codes to
another
so
I see how I am suppose to do it?

I don't do this for a living I am just doing it for an application for
myself...

Thank you so much. (I hope!)









I am pasting 4 lines of text into a text box from a search in
YELLOWPAGES.COM

LastName, FirstName
1234 streetname rd
City, state, 00000-0000
(000-000-0000)

I have 3 parse buttons to fill that text into fields on my form.
Here
is
my
proceedure.
I paste it in my notes field then cut the address through the phone
and
hit
my parse button
then I paste address through end of line and parse2
paste the rest delete city and state and get the zip code and phone
on
the
same
line then parse3 (I don't try to use city and state because they are
a
different table and they are numbers not text in the fields on the
table
this
form is based on)

The following parse buttons work independently but I need to COMBINE
them
and I don't know how. I do NOT KNOW if the code for the end line is
vbCrLf
or
?? and I don't know how to find out. So I understand I may have to
try
different options but my problem is I don't know how to combine the
whole
thing together to even begin.

Can someone help?

Private Sub cmdParse_Click()
On Error GoTo Err_cmdParse_Click

Dim strName As String
Dim strFName As String
Dim strLName As String
Dim intLoc As Integer

Me.Notes.SetFocus
If Me.Notes.Text <> "" Then
strName = Me.Notes.Text
intLoc = InStr(1, strName, " ")
strLName = Left(strName, intLoc - 2)
strFName = Right(strName, Len(strName) - intLoc)
Me.FirstName.SetFocus
Me.FirstName.Text = strFName
Me.LastName.SetFocus
Me.LastName.Text = strLName

End If

Me.Notes = "" 'clears the notes field

Exit_cmdParse_Click:
Exit Sub

Err_cmdParse_Click:
MsgBox Err.Description
Resume Exit_cmdParse_Click

End Sub
'-------------------------------------------------------------------
' cmdParse2_Click()
On Error GoTo Err_cmdParse2_Click

Dim strAddress As String
Dim strFAddress As String
Dim strLAddress As String
Dim intLoc As Integer

Me.Notes.SetFocus
If Me.Notes.Text <> "" Then
strAddress = Me.Notes.Text
intLoc = InStr(1, strAddress, " ")
strLAddress = Left(strAddress, intLoc)
strFAddress = Right(strAddress, Len(strAddress) - intLoc)
Me.streetnumber.SetFocus
Me.streetnumber.Text = strLAddress
Me.StreetName.SetFocus
Me.StreetName.Text = strFAddress
End If
Me.Notes = ""
' --------------rest of parse2 code omited for post
'---------------cmdParse3_Click()
On Error GoTo Err_cmdParse3_Click

Dim strFInfo As String
Dim strPhoneNumber As String
Dim strRPhoneNumber As String
Dim strzipcode As String
Dim strLzipcode As String
Dim intLoc As Integer

Me.Notes.SetFocus
If Me.Notes.Text <> "" Then
strFInfo = Me.Notes.Text
intLoc = InStr(1, strFInfo, " ")
strLzipcode = Left(strFInfo, intLoc)
strRPhoneNumber = Right(strFInfo, Len(strFInfo) - intLoc)
Me.ZipCode.SetFocus
Me.ZipCode.Text = strLzipcode
Me.PhoneNumber.SetFocus
Me.PhoneNumber.Text = strRPhoneNumber
End If
Me.Notes = ""
--------------rest of parse3 code omited for post
Click to expand...
Click to expand...
Click to expand...
 
L

lmv

It's done ... it works I figured it out with your "brilliant" help
Thanks again!

Graham Mandeno said:
You're welcome. Let us know how you get on.
--
Good Luck :)

Graham Mandeno [Access MVP]
Auckland, New Zealand

lmv said:
Haven't worked on it yet but just wanted to say THANK YOU SO MUCH!


Graham Mandeno said:
Hi LMV

That is strange... it looks like the line separator is TWO vbCrLfs. Do
blank lines appear in the Notes textbox when you paste the data?

If this really is the case, then the separator for the purposes of the
Split
function will be vbCrLf & vbCrLf.

Now, the complexity of the parse code depends on how reliable the format
of
the data is. Is it ALWAYS four lines? Is there ALWAYS a comma in the
first
line? does the second line ALWAYS start with a number?

PLeeaassee.. would you mind joining just one of my parse codes to
another
so
I see how I am suppose to do it?

Since you asked so nicely.... ;-)

No matter what the answers to these questions, the following code should
get
you started:

Private Sub cmdParse_Click()
Dim sText As String, i As Integer, s As String
Dim aLines() As String, sLineSeparator As String
sLineSeparator = vbCrLf & vbCrLf
sText = Me.Notes & ""
aLines = Split(Me.Notes, sLineSeparator)
If UBound(aLines) <> 3 Then
MsgBox "Invalid data - exactly 4 lines expected"
Exit Sub
End If
' Line 1: LastName, FirstName
s = aLines(0)
i = InStr(s, ",")
If i = 0 Then
' no comma - no first name
LastName = s
FirstName = Null
Else
LastName = Trim(Left(s, i - 1))
FirstName = Trim(Mid(s, i + 1))
End If
' Line 2: StreetNumber StreetName
s = aLines(1)
If Val(s) > 0 Then
' might have a street number
i = InStr(s, " ")
If i = 0 Then
' no space - no street number
StreetNumber = Null
StreetName = s
Else
StreetNumber = Trim(Left(s, i - 1))
StreetName = Trim(Mid(s, i + 1))
End If
Else
StreetNumber = Null
StreetName = s
End If
' Line 3: City, State ZipCode
s = aLines(2)
i = InStr(s, ",")
If i = 0 Then
' no comma - put it all in city
City = s
State = Null
ZipCode = Null
Else
City = Trim(Left(s, i - 1))
s = Trim(Mid(s, i + 1))
i = InStr(s, " ")
If i = 0 Then
' no space - assume no zip
State = s
ZipCode = Null
Else
State = Trim(Left(s, i - 1))
ZipCode = Trim(Mid(s, i + 1))
End If
End If
' Line 4: Phone number
PhoneNumber = aLines(3)
End Sub


--
Good Luck :)

Graham Mandeno [Access MVP]
Auckland, New Zealand



Graham,
Ok I ran the code and it is {13}{10}{13}{10}
On all lines (nothing on the last phone line at the end)

(Note that you do not need to do a SetFocus to a textbox to reference
or
change its value. You just use the default .Value property instead of
..Text)

I am flying by the seat of my pants here... and I don't know what I
should
replace /and insert in my code.

When you know what the separator is, you can then use the Split
function
to
separate the single string of text into an array of strings, one per
line:

Dim aLines() as String, i as integer
aLines = Split(Me.Notes, vbCrLf) ' assuming this is the separator
For i = 0 to UBound(aLines)
Debug.print i, aLines(i)
Next

Note that the array elements will be numbered starting at 0.

Then you can just parse each line as required.

PLeeaassee.. would you mind joining just one of my parse codes to
another
so
I see how I am suppose to do it?

I don't do this for a living I am just doing it for an application for
myself...

Thank you so much. (I hope!)









I am pasting 4 lines of text into a text box from a search in
YELLOWPAGES.COM

LastName, FirstName
1234 streetname rd
City, state, 00000-0000
(000-000-0000)

I have 3 parse buttons to fill that text into fields on my form.
Here
is
my
proceedure.
I paste it in my notes field then cut the address through the phone
and
hit
my parse button
then I paste address through end of line and parse2
paste the rest delete city and state and get the zip code and phone
on
the
same
line then parse3 (I don't try to use city and state because they are
a
different table and they are numbers not text in the fields on the
table
this
form is based on)

The following parse buttons work independently but I need to COMBINE
them
and I don't know how. I do NOT KNOW if the code for the end line is
vbCrLf
or
?? and I don't know how to find out. So I understand I may have to
try
different options but my problem is I don't know how to combine the
whole
thing together to even begin.

Can someone help?

Private Sub cmdParse_Click()
On Error GoTo Err_cmdParse_Click

Dim strName As String
Dim strFName As String
Dim strLName As String
Dim intLoc As Integer

Me.Notes.SetFocus
If Me.Notes.Text <> "" Then
strName = Me.Notes.Text
intLoc = InStr(1, strName, " ")
strLName = Left(strName, intLoc - 2)
strFName = Right(strName, Len(strName) - intLoc)
Me.FirstName.SetFocus
Me.FirstName.Text = strFName
Me.LastName.SetFocus
Me.LastName.Text = strLName

End If

Me.Notes = "" 'clears the notes field

Exit_cmdParse_Click:
Exit Sub

Err_cmdParse_Click:
MsgBox Err.Description
Resume Exit_cmdParse_Click

End Sub
'-------------------------------------------------------------------
' cmdParse2_Click()
On Error GoTo Err_cmdParse2_Click

Dim strAddress As String
Dim strFAddress As String
Dim strLAddress As String
Dim intLoc As Integer

Me.Notes.SetFocus
If Me.Notes.Text <> "" Then
strAddress = Me.Notes.Text
intLoc = InStr(1, strAddress, " ")
strLAddress = Left(strAddress, intLoc)
strFAddress = Right(strAddress, Len(strAddress) - intLoc)
Me.streetnumber.SetFocus
Me.streetnumber.Text = strLAddress
Me.StreetName.SetFocus
Me.StreetName.Text = strFAddress
End If
Me.Notes = ""
' --------------rest of parse2 code omited for post
'---------------cmdParse3_Click()
On Error GoTo Err_cmdParse3_Click

Dim strFInfo As String
Dim strPhoneNumber As String
Dim strRPhoneNumber As String
Dim strzipcode As String
Dim strLzipcode As String
Dim intLoc As Integer

Me.Notes.SetFocus
If Me.Notes.Text <> "" Then
strFInfo = Me.Notes.Text
intLoc = InStr(1, strFInfo, " ")
strLzipcode = Left(strFInfo, intLoc)
strRPhoneNumber = Right(strFInfo, Len(strFInfo) - intLoc)
Me.ZipCode.SetFocus
Me.ZipCode.Text = strLzipcode
Me.PhoneNumber.SetFocus
Me.PhoneNumber.Text = strRPhoneNumber
End If
Me.Notes = ""
--------------rest of parse3 code omited for post
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Import Complex info from Excel via VBA 6
Search pattern 3
Error: Invalid Argument 3

Top