Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

perl or Simple Excel macro

Posted on 2014-01-28
4
Medium Priority
?
340 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 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

609 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