Extract text out of textbox

[billybanter67]

I have a text box which has peoples address details in it, included in
this one text box is the persons name, address incl post code and
suburb. The reason this is in one text box is that I sell some items
on ebay and I just copy the buyer details and use this code listed
below to paste it into a text box field, from there I copy and paste
it in to each individual field.

Private Sub BillingAddress_DblClick(Cancel As Integer)

On Error GoTo Err_BillingAddress_DblClick
Me.BillingAddress.SetFocus
RunCommand acCmdPaste
EmptyClipboard
Exit_BillingAddress_DblClick:
Exit Sub

Err_BillingAddress_DblClick:
'MsgBox Err.Description
Resume Exit_BillingAddress_DblClick
End Sub

What I want to do is to extract say the person first name and then the
post code.

RICHARD HEWSON
12 SMITH STREET
BURWOOD EAST VIC 3151

So what I want here is to extract the "RICHARD" to a textbox called
firstname and the "3151" to a textbox postcode.


Any ideas?

thanks

 

[Linq Adams via AccessMonster.com]

Assuming that the format is ALWAYS as you've shown it here

Me.txtFirstName = Left(Me.txtAddressDetails, InStr(txtAddressDetails, " "))

Me.txtPostCode = Right(Me.txtAddressDetails, Len(txtAddressDetails) -
(InStrRev(txtAddressDetails, " ")))

 

[billybanter67]

works perfect, thanks for your effort.

Now if I wanted to extract the "BURWOOD EAST " what would I do?
Thanks

 

[ruralguy via AccessMonster.com]

Here's a function for you.
Function Foo(InString As String) As String

Dim a As Long
Dim b As Long
Dim c As Long
Dim d As Long

c = Len(InString)
a = InStrRev(InString, " ")
b = InStrRev(InString, " ", a - 1)
d = InStrRev(InString, Chr(10))

Foo = Mid(InString, d + 1, b - d)

End Function

 

[billybanter67]

Hi again,

Thanks, now I have placed this function in a module.

Could you pls explain the use of this.

Regards
Richard

 

[ruralguy via AccessMonster.com]

Use it like:
Me.txtPostCity = Foo(Me.txtAddressDetails)
and it should pull 'BURWOOD EAST' from your example.

Hi again,

Thanks, now I have placed this function in a module.

Could you pls explain the use of this.

Regards
Richard

Here's a function for you.
Function Foo(InString As String) As String

[quoted text clipped - 12 lines]

End Function