?
Solved

perl or Simple Excel macro

Posted on 2014-01-28
4
Medium Priority
?
337 Views
Last Modified: 2014-01-28
Hi ,

I have data like below.

Detmolder Str. 515 33605 Bielefeld

Should be like this when seperated:

Detmolder Str. 515 , 33605 Bielefeld

So the format schould be:

ADDRESS ,ZIP CITY

So that it takes the address line in 1 column, and the zip + city in 1 column.

That way, if the address line looks like this now, where it has no zip-code, then it will just be the city in the second column.

Like this:

ADDRESS , CITY

Thanks
0
Comment
Question by:magento
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 84

Expert Comment

by:ozo
ID: 39815900
s/(\d+)\s+(\d+)/$1 , $2/
seems to work for your example, but I don't know if there may be other kinds of "ADDRESS" or other kinds of "ZIP CITY" for which another approach may be needed.
0
 
LVL 35

Expert Comment

by:Dan Craciun
ID: 39815903
So how do you know if it has a zip-code?

Are all the cities one word or there can be composite (Frankfurt-am-Main) or multiple word (New York) cities?
0
 
LVL 31

Accepted Solution

by:
farzanj earned 2000 total points
ID: 39815943
This accommodates for ZIP both 5 digit and 9 digit ones and also the absence of it.

echo 'Detmolder Str. 515 33605 Bielefeld' | perl -ne '/(.*) (?:(\d{5}(?:-\d{4})\s+))?(\w+\s?\w*)/; print "$1, $2 $3"'

Open in new window

0
 
LVL 26

Expert Comment

by:wilcoxon
ID: 39816079
What countries are included in the addresses?  Wondering since that will affect detection of zip (some (most?) countries don't use 5-digit postal codes).

Are all the addresses in the format <street> <house number> <zip (optional)> <city>?  As others have asked, can the city be multiple words (such as New York)?
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Six Sigma Control Plans

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question