separate text field into 2 fields

[tybeeisl]

Is there an easy way to separate a text field into to separate fields? I have an address field that needs to have a separate field for the street number and street name.

 

[susanhawk]

Try this:
Private Sub cmdParseAddress_Click()
Dim varParsed As Variant
Dim stNumber As String
Dim stStreet As String
Dim stStreetAddress As String
stStreetAddress = txtAddress
'call function to parse name into array values
'the values of the array in the function "Parse", have to be passed
'to a variant in this procedure which is treated as an array
'handle parsing in function so it is easily reusable code
varParsed = Parse(stStreetAddress)
stNumber = varParsed(1)
stStreet = varParsed(2)
End Sub

Private Function Parse(stFullAddress As String)
'parse street address and load into array
Dim strParseArray(1 To 2) As String
Dim stPartLeft As String, stPart As String
Dim intLength As Integer, intCount As Integer, intMark As Integer
intLength = Len(stFullAddress)
intCount = 1
stPartLeft = Mid(stFullAddress, intCount, intLength)
Do Until stPartLeft = ""
stPart = Mid(stPartLeft, 1, 1)
If IsNumeric(stPart) Then
intMark = intMark + 1
End If
intCount = intCount + 1
stPartLeft = Mid(stFullAddress, intCount, intLength)
Loop
strParseArray(1) = Mid(stFullAddress, 1, intMark)
strParseArray(2) = Mid(stFullAddress, intMark + 2, intLength) 'add two to take out space
Parse = strParseArray
End Function

Hope it works

 

[dwalker]

Assuming the address field is in a table and all the data is already loaded the following solution will separate house number from street name ONLY in those instances when the address format is a number followed by a space and then the remainder of the adddress like "12 My Street". It wont workfor an address like "Unit 1/15 Your Street".

If practicle, consider creating a new table with a Create Table query - base the query on the current table and then add 2 extra columns in the query with the following in the Field row:

Number: Left([address],InStr([address]," &quot-1)

and

Street: Right([address],Len([address])-InStr([address]," &quot)

This assumes your current address field is called [address]. The query will separate the address field into two parts - everything before the first <space> and everything after the first <space>.

As I said, won't work for every address but surely will find most of them.

Regards

Damien

 

[dwalker]

Wow, susanhawk, straight into full on code! Impressive approach! I thought I'd keep it simple with a QBE window.

Looks to me like your code steps through each character and marks the point where a <space> follows a number and then splits the string at that point. This would get around my &quot;Unit 1/15 Your Street&quot; example.

Regards

Damien

 

[tybeeisl]

Many Thanks! All of the addresses should be simple street addresses, so this should work.

Beth