Can a macro help "tidy up" address data?

[Leila-Marie]

<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:HyphenationZone>21</w:HyphenationZone> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--> Hi,


Today I’m trying to work out, if I can write or record a macro to help me tidy up some data.


The data is the addresses of a long, long list of companies.

In its current format, the data has 2 columns in each row. The first column contains the “Street” and “House number” and the second column contains the “Postal Code” and the “City”.

What I need to do is to separate these, so that I have 4 columns – a separate one for each piece of information.

(The reason for this is that they will be imported into a CRM database and that program needs these pieces of information separately.)

So, I’ve already developed a macro for tidying up the “Postal Code and City” cells. That was pretty easy.

However, the “Street and House number” cells are much trickier.

Sometimes the Street name is just one word, sometimes two words, sometimes three words.

And the “House number” is not always just a number. Sometimes there are numbers like “17 c” or “16-18” or “42 – 43” or “34 a – c”.

So it’s quite messy…

I’m working in Europe (Germany) and here the street names come BEFORE the house number (as opposed to in English-speaking countries, where the reverse is true.)

So, I’m thinking whether I can tell a macro to cut and paste “everything before the first number in the cell” (this would be the street name) and to cut and paste “the first number in the cell and everything that comes after it” somewhere else.

Alternatively, if that’s too tricky, I could also get each word of the street name and each number / letter of the house number to be posted into separate cells – that way I could maybe tell a macro to check whether the contents of a cell are words or numbers, but I don’t really know if that would make it any less tricky.

Many thanks for any of your thoughts!

Leila-Marie
<!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Normale Tabelle"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]-->

 

[DeusXv]

Btw can you paste the names of the streets into a reply instead of me making up names like "German street" cos I couldn't copy the names from the pic

 

[DeusXv]

K i think I solved it try the below:

Excel 2007
	A	B	C
1	Full Address	Street	No.
2	German Str. 10b	German Str.	10b
3	German 9 c	German	9 c
4	German 9	German	9
5	German Str Cool 5b	German Str Cool	5b
Sheet1

Cell Formulas
Range	Formula
B2	=TRIM(LEFT(A2, MIN(FIND({"0","1","2","3","4","5","6","7","8","9"}, A2 & 1234567890) -1) ) )
B3	=TRIM(LEFT(A3, MIN(FIND({"0","1","2","3","4","5","6","7","8","9"}, A3 & 1234567890) -1) ) )
B4	=TRIM(LEFT(A4, MIN(FIND({"0","1","2","3","4","5","6","7","8","9"}, A4 & 1234567890) -1) ) )
B5	=TRIM(LEFT(A5, MIN(FIND({"0","1","2","3","4","5","6","7","8","9"}, A5 & 1234567890) -1) ) )
C2	=MID(A2, MIN(FIND({"0","1","2","3","4","5","6","7","8","9"}, A2 & 1234567890) ), 100)
C3	=MID(A3, MIN(FIND({"0","1","2","3","4","5","6","7","8","9"}, A3 & 1234567890) ), 100)
C4	=MID(A4, MIN(FIND({"0","1","2","3","4","5","6","7","8","9"}, A4 & 1234567890) ), 100)
C5	=MID(A5, MIN(FIND({"0","1","2","3","4","5","6","7","8","9"}, A5 & 1234567890) ), 100)

 

[Michael M]

I'm no expert at this but try this
Copy the code into a Standard Module and then use....change cell reference to suit
Please forgive my translations to suit the requirements

=NDC(A1)

Function NDC(s As String) As String
With CreateObject("VBScript.RegExp")
  .Pattern = "(\(.|\d[0-9A-z -]+)"
  If .Test(s) Then NDC = .Execute(s)(0).SubMatches(0)
End With
End Function

Excel 2007
	A	B
1	konigsberger Str.255	255
2	ldigberg 56	56
3	long flagge 15	15
4	grevenr Str.44	44
5	emil-figg--str.76-80	76-80
6	Am lowig 2 c	2 c
7	rhine 6-8	6-8
8	DR -pickard-str.17 g	17 g
9	Angelhome startte 20b	20b
Sheet1

Cell Formulas
Range	Formula
B1	=NDC(A1)
B2	=NDC(A2)
B3	=NDC(A3)
B4	=NDC(A4)
B5	=NDC(A5)
B6	=NDC(A6)
B7	=NDC(A7)
B8	=NDC(A8)
B9	=NDC(A9)

 

[Michael M]

Sorry, forgot the rest of it...
Same as above, but use this to get the street names

Function NBC(s As String) As String 'Splits German Street Names
With CreateObject("VBScript.RegExp")
  .Pattern = "(\w[A-z -.]+)"
  If .Test(s) Then NBC = .Execute(s)(0).SubMatches(0)
End With
End Function

 

[Leila-Marie]

I got close to the answer like I can split the number away from the cell but the names is the weird one here is what I have below:

Excel 2007

	A	B	C
1	German Str. 10b	German Str.	10b
2	German 9 c	German 9	9 c
3	German 9	German 9	9

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula B1 =MID(A1,1,FIND(" ",A1,FIND(" ",A1)+1)) C1 =MID(A1, MIN( FIND({"0","1","2","3","4","5","6","7","8","9"}, A1 & 1234567890) ), 100) B2 =MID(A2,1,FIND(" ",A2,FIND(" ",A2)+1)) C2 =MID(A2, MIN( FIND({"0","1","2","3","4","5","6","7","8","9"}, A2 & 1234567890) ), 100) B3 =MID(A3,1,FIND(" ",A3,FIND(" ",A3)+1)) C3 =MID(A3, MIN( FIND({"0","1","2","3","4","5","6","7","8","9"}, A3 & 1234567890) ), 100) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

OMG OMG OMG - I just got it to work!!!!

I had to play around a little with the formulae you suggested, Deus, but I got there, after about 2 hours...!

I've used the formula you had in C2 and then adjusted that slightly, to give me the right result in B2...

First of all, what I had to work out (again, cos I'd forgotten, oops) was to change the formula names into German and replace all of the commas with semicolons...

Then, I simply switched around the values you put in the overall formula in C2, so that it takes the contents of A2, and starts at one and continues to the FIND(MIN)-2...

By "skipping back" 2 spaces, from the FIND value that produced the first "number" of the house-number, it took me exactly to the end of the street name, no matter how many words were in the street name...

I'll try posting (and converting back to English-Excel) what finally ended up working...

THANK YOU SO MUCH!!!!!

 

[Leila-Marie]

So - just so I don't get myself completely confused, I will try posting in German-Excel what I got to work, in the end...

	A	B	C
1	Pappelweg 10	Pappelweg	10
2	Christian-Rath-Straße 4	Christian-Rath-Straße	4
3	Ängelholmer Straße 20b	Ängelholmer Straße	20b
4	Grevener Str. 44	Grevener Str.	44
5	Lange Flagge 15	Lange Flagge	15
6	Emil-Figge-Str. 76 - 80	Emil-Figge-Str.	76 - 80
7	An der Vaerstbrücke 1	An der Vaerstbrücke	1
8	Rheinstraße 6-8	Rheinstraße	6-8

<tbody>
</tbody>

 

[Leila-Marie]

Ach, sorry - I can't make those tables look very nice... I don't really get the table editing function, here...

Anyway, in Column B, I used:

=TEIL(A1; 1; MIN( FINDEN({"0";"1";"2";"3";"4";"5";"6";"7";"8";"9"}; A1 & 1234567890))-2)

etc...

which translates into English as:

=MID(A1, 1, MIN( FIND({"0","1","2","3","4","5","6","7","8","9"}, A1 & 1234567890))-2)

and in Column C, I used the formula you used:

=MID(A2, MIN( FIND({"0","1","2","3","4","5","6","7","8","9"}, A2 & 1234567890) ), 100)

which was this in German:

=TEIL(A1; MIN( FINDEN({"0";"1";"2";"3";"4";"5";"6";"7";"8";"9"}; A1 & 1234567890) ); 100)

 

[Leila-Marie]

OMG I have just been figuring this stuff out by myself - and didn't even realised you'd been posting in the meantime...

Btw can you paste the names of the streets into a reply instead of me making up names like "German street" cos I couldn't copy the names from the pic

Sorry - I only figured out how to post tables here, afterwards...!! This goes beyond embarassing, now... haha

 

[Leila-Marie]

Sorry, forgot the rest of it...
Same as above, but use this to get the street names

Function NBC(s As String) As String 'Splits German Street Names
With CreateObject("VBScript.RegExp")
  .Pattern = "(\w[A-z -.]+)"
  If .Test(s) Then NBC = .Execute(s)(0).SubMatches(0)
End With
End Function

Hi Michael - that looks great. I will try your code as soon as I've recovered from the gruelling work of working out the functions that (seem) to have gotten it to work...
Thank you so much!
XXX
Leila-Marie

 

[Michael M]

Have a look at my tag and download the HTML Maker for future use.
This will allow you to insert your data here much easier.
Keep in mind I have provided 2 different functions.....one for the numbers and one for the street names !!