excel - address problem

[SQLScholar]

Hey,

I have a spreadsheet with a coloumn which has data such as

15 the street,
the city,
the county,
Postcode

Now the problem comes (p.s. i didnt make this sheet) now i want to import it in to map point and i need all these addresses separeted out (i.e. each parameter in a different coloumn).

Anyone got a automatic way to do this, otherwise its going to take me weeks :-(

TIA

Dan ----------------------------------------
There are 2 types of computer, the prototype and the obsolete!!

 

[RobBroekhuis]

Sure. You'll need to specify more explicitly how the data is arranged now - it should take just minutes to convert it.
Rob

 

[SQLScholar]

Expample as below

15 closed Street,
the town,
the area,
IP3 LEE

What other information do you need? ----------------------------------------
There are 2 types of computer, the prototype and the obsolete!!

 

[RobBroekhuis]

Presumably your spreadsheet has more than just one address - how are they arranged? Directly below each other in one column? If so, the following should work:

dim cell1 as range, cell2 as range
set cell1=range("A1&quot
worksheets.add
set cell2=range("A1&quot
do while cell1<>&quot;&quot;
cell2=cell1
cell2.offset(0,1)=cell1.offset(1,0)
cell2.offset(0,2)=cell1.offset(2,0)
cell2.offset(0,3)=cell1.offset(3,0)
set cell1=cell1.offset(4,0)
set cell2=cell2.offset(1,0)
loop

This creates a new worksheet with the rearranged data.
Rob

 

[SQLScholar]

Thanks... but i will just clarify what you have said and make sure the code is correct for the job.

The example address shown is in one cell, with line breaks.

and yes there is lots more then 1.... say 500!!

Thanks ever so much for your help. If you can just let me know if this code is the code i want (if not i will try later)

TIA

Dan ----------------------------------------
There are 2 types of computer, the prototype and the obsolete!!

 

[SQLScholar]

tried it. It seems to be not for the conditions above.

Any help much appreciated ----------------------------------------
There are 2 types of computer, the prototype and the obsolete!!

 

[Nikita6003]

Plank,

try using this bit of VBA to parse out the addresses. It assumes that you've used ALT-ENTER to create the line feeds in your cells!

Dim l_iCounter As Integer
Dim l_iParseOut As Integer
Dim l_sCompleteAddress As String


'Take complete Address
l_sCompleteAddress = ThisWorkbook.Sheets(1).Cells(1, 1)

'loop 4 times as there are 4 ALT-ENTERS
For l_iCounter = 1 To 3
'Find ALT-ENTER
l_iParseOut = InStr(1, l_sCompleteAddress, Chr(10))
'take the address part - excluding the ALT-ENTER - & report in the next columns
Cells(1, 1 + l_iCounter) = Mid(l_sCompleteAddress, 1, l_iParseOut - 1)
'Now remove the address part from your temp address variable
l_sCompleteAddress = Right(l_sCompleteAddress, Len(l_sCompleteAddress) - l_iParseOut)
Next l_iCounter
Cells(1, 1 + l_iCounter) = l_sCompleteAddress


Hope it helps

Cheers
Nikki
;-)

 

[Nikita6003]

Plank

in my previous post ignore the comment 'loop 4 times as there are 4 ALT-ENTERS! The last line in your cell is whatever's left after 3 loops so it's simply a matter of &quot;you only loop thrice&quot; ;-)

Cheers
Nikki

shaken, not stirred

 

[SQLScholar]

Thanks for your help but

Dim l_iCounter As Integer
Dim l_iParseOut As Integer
Dim l_sCompleteAddress As String


'Take complete Address
l_sCompleteAddress = ThisWorkbook.Sheets(1).Cells(1, 1)

'loop 4 times as there are 4 ALT-ENTERS
For l_iCounter = 1 To 3
'Find ALT-ENTER
l_iParseOut = InStr(1, l_sCompleteAddress, Chr(10))
'take the address part - excluding the ALT-ENTER - & report in the next columns
[Bold] Cells(1, 1 + l_iCounter) = Mid(l_sCompleteAddress, 1, l_iParseOut - 1)[/bold]
'Now remove the address part from your temp address variable
l_sCompleteAddress = Right(l_sCompleteAddress, Len(l_sCompleteAddress) - l_iParseOut)
Next l_iCounter
Cells(1, 1 + l_iCounter) = l_sCompleteAddress

with the error

invalid precedure call or arguement.

I am not very good programming in VBA. Do i need to adapt the code, or select the cells i want??

Thanks ----------------------------------------
There are 2 types of computer, the prototype and the obsolete!!

 

[RobBroekhuis]

when the code execution interrupts, hit &quot;debug&quot; and check the values of l_icounter and l_iparseout. My guess is that l_iparseout is zero at that point. Is your first address in cell A1 on the first sheet? (That's what Nikki's code assumes)
Rob

 

[Nikita6003]

Plank,

it could very well be that your cell doesn't actually contain an ALT-ENTER (that's the assumption I made)
The &quot;invalid procedure call or argument&quot; error results from the InStr(1, l_sCompleteAddress, Chr(10)) function returning 0 which means that the MID on the next line (which is the one you marked) fails.

How have you set up the addresses in each of the cells that need parsing - what separates each of the address lines within the cell?

Cheers
Nikki

 

[Zathras]

Writing a VBA macro is not always the best way to go. I had to convert addresses to a MapPoint format also, and the formats of the addresses were not totally consistent. In a case like this, worksheet text formulas often are quicker to use in the long run. You can eyeball the results and make additional adjustments as needed.

That said, try this. It assumes that each address is in one cell in column A. The example you gave shows the delimiter between the lines as a three-character sequence: comma + space + line feed. If it turns out in fact to be something else, this will allow you to &quot;play&quot; with it:

Here goes:

1. Have the addresses in column A, rows 2 thru however many there are. All other columns and row 1 should be blank.

2. Put the following formulas in the cells indicated:

B1: =&quot;, &quot;&CHAR(10)
B2: 1
C2: =IF(ISERROR(FIND($B$1,$A2,B2)),LEN($A2),FIND($B$1,$A2,B2))+LEN($B$1)
G2: =MID($A2,B2,C2-B2-LEN($B$1))

3. Copy the formula from C2 into D2 thru F2

4. Copy the formula from G2 into H2 thru J2

You should now see the first address parsed in G2 thru J2. If not, the value in B1 may need to be tweaked.

If all is well, copy the formulas from b2 thru j2 down the spreadsheet. then do a copy, paste values into a new sheet and carry on.

Good luck!

 

[Zathras]

Writing a VBA macro is not always the best way to go. I had to convert addresses to a MapPoint format also, and the formats of the addresses were not totally consistent. In a case like this, worksheet text formulas often are quicker to use in the long run. You can eyeball the results and make additional adjustments as needed.

That said, try this. It assumes that each address is in one cell in column A. The example you gave shows the delimiter between the lines as a three-character sequence: comma + space + line feed. If it turns out in fact to be something else, this will allow you to &quot;play&quot; with it:

Here goes:

1. Have the addresses in column A, rows 2 thru however many there are. All other columns and row 1 should be blank.

2. Put the following formulas in the cells indicated:

B1: =&quot;, &quot;&CHAR(10)
B2: 1
C2: =IF(ISERROR(FIND($B$1,$A2,B2)),LEN($A2),FIND($B$1,$A2,B2))+LEN($B$1)
G2: =MID($A2,B2,C2-B2-LEN($B$1))

3. Copy the formula from C2 into D2 thru F2

4. Copy the formula from G2 into H2 thru J2

You should now see the first address parsed in G2 thru J2. If not, the value in B1 may need to be tweaked.

If all is well, copy the formulas from b2 thru j2 down the spreadsheet. then do a copy, paste values into a new sheet and carry on.

Good luck!

 

[Zathras]

Sorry for the double post, but I got the server message that said it didn't go thru the first time and that I should try again.