Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
seperate components of a whole address entry
Hi there
I am looking for ways to: seperate a whole address entry into different components like: number & street name suburb state postcode It would be highly appreciated if you can help . I looked everywhere in help sections but nothing available for this.The only available is seperate first name and last name. I have apply the same formula but does not work. Kind regards -- Telly |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
seperate components of a whole address entry
Please post few samples
-- If this post helps click Yes --------------- Jacob Skaria "telly" wrote: Hi there I am looking for ways to: seperate a whole address entry into different components like: number & street name suburb state postcode It would be highly appreciated if you can help . I looked everywhere in help sections but nothing available for this.The only available is seperate first name and last name. I have apply the same formula but does not work. Kind regards -- Telly |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
seperate components of a whole address entry
Hi Jocob
I really appreciate your prompt response. Look at this address list which is in excel. I need to seperate the street number and name from the suburb and the suburb from the state and the state from the postcode. You have no idea what sort of problem that you can resolve if you answer. example: 1 - first addres street number&name (11 GAVIN PL) (Suburb KINGS LANGLEY ) State (NSW) Post code (2147) example: 2 - fourth address street number&name (PO BOX 128) Suburb ( MEENIYAN) State (VIC) Post code (3956) here are a short list of address 11 GAVIN PL KINGS LANGLEY NSW 2147 27/1 BRIDGEMAN DR REEDY CREEK QLD 4227 65/3 BRIDGMAN DR REEDY CREEK QLD 4227 PO BOX 360 MENAI CENTRAL BANGOR NSW 2234 PO BOX 128 MEENIYAN VIC 3956 25/1 BRIDGMAN DR REEDY CREEK QLD 4227 kind regards -- Telly "Jacob Skaria" wrote: Please post few samples -- If this post helps click Yes --------------- Jacob Skaria "telly" wrote: Hi there I am looking for ways to: seperate a whole address entry into different components like: number & street name suburb state postcode It would be highly appreciated if you can help . I looked everywhere in help sections but nothing available for this.The only available is seperate first name and last name. I have apply the same formula but does not work. Kind regards -- Telly |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
seperate components of a whole address entry
Without using a macro you will have to do this in sections..Assuming you have
the addresses in ColA...try the below.. 1. In B1 enter the below formula which will return the number of spaces in between. =LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(32),)) 2. Sort the entire list by ColB so that addresses with the same number of spaces come together.You might end up in 3 or 4 categories with 5,6,7 spaces 3. Now copy the first section of address (with 5 spaces) and paste that to a differnt sheet. From menu DataText to columnsNextSpace Delimiter NextFinnish. will split that to different columns..Using CONCATENATE function you will need to combine some fields... 4. Repeat the same for the ones with 6 and 7 spaces.. If this post helps click Yes --------------- Jacob Skaria "telly" wrote: Hi Jocob I really appreciate your prompt response. Look at this address list which is in excel. I need to seperate the street number and name from the suburb and the suburb from the state and the state from the postcode. You have no idea what sort of problem that you can resolve if you answer. example: 1 - first addres street number&name (11 GAVIN PL) (Suburb KINGS LANGLEY ) State (NSW) Post code (2147) example: 2 - fourth address street number&name (PO BOX 128) Suburb ( MEENIYAN) State (VIC) Post code (3956) here are a short list of address 11 GAVIN PL KINGS LANGLEY NSW 2147 27/1 BRIDGEMAN DR REEDY CREEK QLD 4227 65/3 BRIDGMAN DR REEDY CREEK QLD 4227 PO BOX 360 MENAI CENTRAL BANGOR NSW 2234 PO BOX 128 MEENIYAN VIC 3956 25/1 BRIDGMAN DR REEDY CREEK QLD 4227 kind regards -- Telly "Jacob Skaria" wrote: Please post few samples -- If this post helps click Yes --------------- Jacob Skaria "telly" wrote: Hi there I am looking for ways to: seperate a whole address entry into different components like: number & street name suburb state postcode It would be highly appreciated if you can help . I looked everywhere in help sections but nothing available for this.The only available is seperate first name and last name. I have apply the same formula but does not work. Kind regards -- Telly |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
seperate components of a whole address entry
Hi Jacob
first i appreciate your prompt response and second your answer which makes sence to me . i will try and see how I go. Thank you again God bless you mate! -- Telly "Jacob Skaria" wrote: Without using a macro you will have to do this in sections..Assuming you have the addresses in ColA...try the below.. 1. In B1 enter the below formula which will return the number of spaces in between. =LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(32),)) 2. Sort the entire list by ColB so that addresses with the same number of spaces come together.You might end up in 3 or 4 categories with 5,6,7 spaces 3. Now copy the first section of address (with 5 spaces) and paste that to a differnt sheet. From menu DataText to columnsNextSpace Delimiter NextFinnish. will split that to different columns..Using CONCATENATE function you will need to combine some fields... 4. Repeat the same for the ones with 6 and 7 spaces.. If this post helps click Yes --------------- Jacob Skaria "telly" wrote: Hi Jocob I really appreciate your prompt response. Look at this address list which is in excel. I need to seperate the street number and name from the suburb and the suburb from the state and the state from the postcode. You have no idea what sort of problem that you can resolve if you answer. example: 1 - first addres street number&name (11 GAVIN PL) (Suburb KINGS LANGLEY ) State (NSW) Post code (2147) example: 2 - fourth address street number&name (PO BOX 128) Suburb ( MEENIYAN) State (VIC) Post code (3956) here are a short list of address 11 GAVIN PL KINGS LANGLEY NSW 2147 27/1 BRIDGEMAN DR REEDY CREEK QLD 4227 65/3 BRIDGMAN DR REEDY CREEK QLD 4227 PO BOX 360 MENAI CENTRAL BANGOR NSW 2234 PO BOX 128 MEENIYAN VIC 3956 25/1 BRIDGMAN DR REEDY CREEK QLD 4227 kind regards -- Telly "Jacob Skaria" wrote: Please post few samples -- If this post helps click Yes --------------- Jacob Skaria "telly" wrote: Hi there I am looking for ways to: seperate a whole address entry into different components like: number & street name suburb state postcode It would be highly appreciated if you can help . I looked everywhere in help sections but nothing available for this.The only available is seperate first name and last name. I have apply the same formula but does not work. Kind regards -- Telly |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
seperate components of a whole address entry
On Thu, 28 May 2009 21:30:01 -0700, telly wrote:
Hi Jocob I really appreciate your prompt response. Look at this address list which is in excel. I need to seperate the street number and name from the suburb and the suburb from the state and the state from the postcode. You have no idea what sort of problem that you can resolve if you answer. example: 1 - first addres street number&name (11 GAVIN PL) (Suburb KINGS LANGLEY ) State (NSW) Post code (2147) example: 2 - fourth address street number&name (PO BOX 128) Suburb ( MEENIYAN) State (VIC) Post code (3956) here are a short list of address 11 GAVIN PL KINGS LANGLEY NSW 2147 27/1 BRIDGEMAN DR REEDY CREEK QLD 4227 65/3 BRIDGMAN DR REEDY CREEK QLD 4227 PO BOX 360 MENAI CENTRAL BANGOR NSW 2234 PO BOX 128 MEENIYAN VIC 3956 25/1 BRIDGMAN DR REEDY CREEK QLD 4227 kind regards -- Telly This can be quite a difficult problem, unless you have some standardized method of determining where the street numbername ends, and the suburb begins. Alternatively, and workable in the US (but I don't know about Australia), you could determine the suburb name by doing a lookup on the postcode. The algorithm for the postcode is simple since the postcode is always the last set of digits in the line. And the State is the string of capitalized letters just before that. In your examples, it is the case that the StreetNumberName is either a PO Box ending with a Number; or a true street address ending with DR or PL. If that is always the case (and you could certainly add on some additional street name terminations (see below), then a macro could parse out the segments. There are instructions within the macro as to how to add more terminators. The macro, as written, assumes all capital letters, single spaces between words, and no spaces at the beginning or end of the string. This could be changed if necessary. If there are too many exceptions to the PO BOX or Street Terminator rule, you'll have to do a lookup on the postcode to get the Suburb, and parse things out that way. To enter this Macro (Sub), <alt-F11 opens the Visual Basic Editor. Ensure your project is highlighted in the Project Explorer window. Then, from the top menu, select Insert/Module and paste the code below into the window that opens. To use this Macro (Sub), first select the range to parse. Then <alt-F8 opens the macro dialog box. Select the macro by name, and <RUN. ============================================= Option Explicit Sub ParseAUaddress() Dim c As Range, rg As Range Dim S As String Dim re As Object, mc As Object, m As Object Dim i As Long Set re = CreateObject("vbscript.regexp") 'Note the enclosed pipe delimited list of StreetNumberName 'terminators below: (PL|DR) 'To expand this, be sure to add further pipe delimited phrases 'within the parentheses like this: (PL|DR|AV|ST) re.Pattern = "^(.*?\s(PL|DR)|PO\sBOX\s\d+)\s(.*?)\s([A-Z]+)\s(\d+)$" Set rg = Selection 'or whatever For Each c In rg With c Range(.Offset(0, 1), .Offset(0, 4)).ClearContents S = .Value If re.test(S) Then Set mc = re.Execute(S) For Each m In mc .Offset(0, 1).Value = m.submatches(0) 'StreetNumberName .Offset(0, 2).Value = m.submatches(2) 'Suburb .Offset(0, 3).Value = m.submatches(3) 'State .Offset(0, 4).Value = m.submatches(4) 'PostCode Next m End If End With Next c End Sub ============================== --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Seperate Name, Street Address & City, State Zip | Excel Worksheet Functions | |||
Matching address with two seperate worksheets | Excel Worksheet Functions | |||
Validate Email address entry | Excel Discussion (Misc queries) | |||
Displaying data from seperate sheet upon drop down box entry | Excel Worksheet Functions | |||
automatic address and phone cell entry | Excel Worksheet Functions |