Paste Full Address - Slit into differant fields for the db. (1 Viewer)

[AnthonyGerrard]

I was just doing a bit of data entry - copying an address from a website to my application.

I had to line by line move the address to the corect fields.

What I woudl like to do - sf maybe copy and paste the whole address and have it automtically split.

So basically each line would be split and analysed.

I could lookup and recognise, county names, town names and allocate these correctly. I could probably also recognise a postcode line and allocate this accordingly. THen any lines above teh town line, could be allocated to my address1, address2 etc fields.

Has anyone alsready got , know of a function that does this before - I reinvent the wheel? THanks

 

[CJ_London]

there are plenty of already invented wheels you could use - all depends on the data as received.

For example if the data is received with linefeeds or similar characters - eg

1 some road
sometown
somecountry

or perhaps

1 some road,sometown,somecountry

you can use the vba split function

Alternatively, as you say, you could perhaps do it based on recognising town and county names (bearing in mind these can be abbreviated)

difficult to advise without seeing some sample data to get a feel for the range of data and the actual process you are using - do you copy/paste an address at a time, or bulk copy addresses for example. Are they part of a larger 'copy/paste' (i.e. includes names, phone numbers) or just addresses? are they copied from the web or excel or somewhere else? etc

Whichever basis is used, it would almost certainly involve pasting to a text box on a form with code behind it or a separate button to do the split.

 

[AnthonyGerrard]

there are plenty of already invented wheels you could use - all depends on the data as received.

For example if the data is received with linefeeds or similar characters - eg

1 some road
sometown
somecountry

or perhaps

1 some road,sometown,somecountry

you can use the vba split function

Alternatively, as you say, you could perhaps do it based on recognising town and county names (bearing in mind these can be abbreviated)

difficult to advise without seeing some sample data to get a feel for the range of data and the actual process you are using - do you copy/paste an address at a time, or bulk copy addresses for example. Are they part of a larger 'copy/paste' (i.e. includes names, phone numbers) or just addresses? are they copied from the web or excel or somewhere else? etc

Whichever basis is used, it would almost certainly involve pasting to a text box on a form with code behind it or a separate button to do the split.

It would be an address at a time. I would hope teh end user would be bright enought not to copy/paste teh first line if that was a a persons name.

Thanks

 

[CJ_London]

Not sure if you are still looking for a solution or if what I provided was sufficient. If still looking, please provide answers to my questions and some example data to demonstrate the range of data

 

[AnthonyGerrard]

Heres an address I was working with , my first challenge is to split it into each line? THanks

Company Name (Registered Office)
35 Scirocco Close
Moulton Business Park
Northampton
Northamptonshire
NN3 6AP

 

[CJ_London]

Please provide what I asked for, I don't have time to drag it out of you

I asked

some sample data to get a feel for the range of data and the actual process you are using

this means 10 or 15 addresses - or can I take it as read that the address will always be 5 rows?
you say

my first challenge is to split it into each line

if that is how it comes then it is already split, just need to identify the appropriate characters.

I asked

are they copied from the web or excel or somewhere else?

this determines the carriage return characters used

 

[Uncle Gizmo]

Cleaning up UK address is as usually a problem because there's no fixed length or fix design to an address.

I would emphasize what Chris has already said you need to provide a set of addresses (taken from the website would be best) this will give you a better idea of the complexity.

Sent from my SM-G925F using Tapatalk

 

[AnthonyGerrard]

As you point out the address could be in any format - could be 4 line, 3, could have country , postcode not etc etc.

Again it could come from any source, so i'm just trying to detect a carriage return character.

Right I think I'm under way, splitting field !One by VbCtlf


Private Sub Command18_Click()
On Error GoTo Err_Command18_Click

Dim wAddress As String
Dim X As Variant, I As Integer
wAddress = Me!One


MsgBox wAddress


X = Split(wAddress, vbCrLf)
I = 0
Do While I < UBound(X)
MsgBox X(I)
I = I + 1
Loop
Exit_Command18_Click:
Exit Sub

Err_Command18_Click:
MsgBox Err.Description
Resume Exit_Command18_Click

End Sub

THanks

 

[CJ_London]

it could come from any source

OK, be aware that there are a number of different carriage return options and they are completely different for web pages

good luck with your project

 

[Uncle Gizmo]

An approach I used, which I found quite successful was to start at both ends. You can usually assume that the company name or the person name is the first entry, and that the last entry will be the postcode. Then you work in from both ends, and leave gaps in the middle where there are no entries. (Assuming that you want to move each individual address line into its own field within a table)...

Sent from my SM-G925F using Tapatalk

 

[jdraw]

Anthony,
Can you show us code you are using for, or more info about

copying an address from a website to my application

.

 

[AnthonyGerrard]

Anthony,
Can you show us code you are using for, or more info about
.

I cant no, I wish I could - its from whatever source in whatever format an end user gets an address and tries to paste it. I suppose mainly it will be stright from a compnay website, or email.

Heres the bones of what I have, teh main problem may be recognising differant carrriage returns?

Thanks all

X = Split(wAddress, vbCrLf)
I = 0
Do While I <= UBound(X)
       MsgBox X(I)
       '''Detect if its a town
       If Nz(DLookup("OrganisationID", "Organisation", "Town = '" & X(I) & "'"), 0) > 0 Then
       
            MsgBox "Its a town!"
            Me!Town = X(I)
            ''''now see how many line are before teh town an put them into appropriate feilds
            Itown = 0
            Do While Itown < I
                For Each C In Me.Controls
                  Select Case C.Name
                    Case CStr(Itown + 1)
                        C = X(Itown)
                  End Select
                Next C
             Itown = Itown + 1
            Loop
       End If
       ''Detect if its a county
        If Nz(DLookup("CountyID", "County", "CountyName = '" & X(I) & "'"), "") <> "" Then
        Me!County = X(I)

       End If
       'Detect if its a poscode
       '' If Nz(DLookup("CountyID", "County", "CountyName = '" & X(I) & "'"), "") <> "" Then
       
        ''MsgBox "Its a town!"
       ''End If
        If Nz(DLookup("CountryID", "Country", "Country = '" & X(I) & "'"), 0) <> 0 Then
            Me!Country = X(I)

       End If
        I = I + 1
Loop

 

[jdraw]

Could you provide a url/link to a website you are testing with?

I cant no, I wish I could - its from whatever source in whatever format an end user gets an address and tries to paste it.

So are you saying the user does a manual cut and paste?

 

[arnelgp]

here i tried to recreate your scenario.
open form1 and paste address at first textbox (big one).
note though that this is not complete since there is no validation for comboboxes.
don't select ID from combo since this is autonumber field.

 

[AnthonyGerrard]

Could you provide a url/link to a website you are testing with?


So are you saying the user does a manual cut and paste?

Yes , thats what they will be doing.

 

[AnthonyGerrard]

here i tried to recreate your scenario.
open form1 and paste address at first textbox (big one).
note though that this is not complete since there is no validation for comboboxes.
don't select ID from combo since this is autonumber field.

Cheers mate, I hope the code I just pasted is hopefully basically it.