Leila-Marie
Board Regular
- Joined
- Aug 23, 2013
- Messages
- 50
<!--[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]-->
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]-->
