?
Solved

perl or Simple Excel macro

Posted on 2014-01-28
4
Medium Priority
?
345 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
4 Comments
 
LVL 85

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 27

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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Here is why.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Six Sigma Control Plans

579 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