Data to coloumns - separate after zip code

**ZUZ3L** · 12-01-2010, 07:39 AM

Hi guys

First thread on EF... I've been browsing for a while.

I have a list where the zip code (4 digits) is within the adress coloumn. I would like it to be in seperate columns:

Now it's like this:
Company name; Adress
Output: Blueblau; Vardegade 23 5. 2100 Copenhagen East

I would like it to be:
Company name; Adress; Zip code; City
Output: Blueblau; Vardegade 23 5.; 2100; Copenhagen East

How can I do this??

Thank you in advance...

**watersev** · 12-01-2010, 08:36 AM

it would be nice to see example of data in excel

**ZUZ3L** · 12-01-2010, 08:40 AM

Originally Posted by watersev

it would be nice to see example of data in excel

Hi... The example is attached... Thank you... :-)

**Marcol** · 12-01-2010, 09:29 AM

Try this workbook

Hope this helps

**ZUZ3L** · 12-01-2010, 10:01 AM

Originally Posted by Marcol

Try this workbook

Hope this helps

It works on 90% of all the adresses, but alot of them, doesn't work, and I can't seem to see why...?

I have attached a sheet with the ones highlighted that doesn't work.

Thank you again... You help is much apriciated! :-) Thank you!

**Marcol** · 12-01-2010, 10:44 AM

Because your addresses are quite variable I think that a UDF might be the easiest solution.

Please Login or Register  to view this content.

Enter this in D2

Please Login or Register  to view this content.

In C2

Please Login or Register  to view this content.

in E2

Please Login or Register  to view this content.

Drag/Fill these down as required.

This will return the last group of 4 digits in the string.

Hope this helps

**ZUZ3L** · 12-01-2010, 10:54 AM

It worked like a CHARM! Thank you very much!! :-)

I really apriciate your help, and it troubles me that I'm asking you yet another question.
I thought I could do this my self, but it doesn't work.

I want to delete everything after the company name, from and with "V/"...
Ex.:
Arcasa ApS Arkitekter Par
arkitekter V/ Thomas Andersen
M arkitekter A/S
0205 I/S v/ Peter Eriksen Og Birgitte Neergaard
1:1 Arkitekter ApS
1:1 Landskab ApS
1:1 V/ Sigurd Elling

Will be:
Arcasa ApS Arkitekter Par
arkitekter
M arkitekter A/S
0205 I/S
1:1 Arkitekter ApS
1:1 Landskab ApS
1:1

How is that possible?

Thank you in advance...

**Marcol** · 12-01-2010, 11:21 AM

Try this

Please Login or Register  to view this content.

Hope this helps

**ZUZ3L** · 12-02-2010, 02:18 AM

Originally Posted by Marcol

Try this

Please Login or Register  to view this content.

Hope this helps

Thank you SO much... I really apriciate your help! Thank you! :-)

**Marcol** · 12-02-2010, 05:53 AM

Happy to have been of some help.

Data to coloumns - separate after zip code

LinkBack

Thread Tools

Rate This Thread

Display

Data to coloumns - separate after zip code

Re: Data to coloumns - seperate after zip code

Re: Data to coloumns - seperate after zip code

Re: Data to coloumns - seperate after zip code

Re: Data to coloumns - seperate after zip code

Re: Data to coloumns - seperate after zip code

Re: Data to coloumns - seperate after zip code

Re: Data to coloumns - seperate after zip code

Re: Data to coloumns - seperate after zip code

Re: Data to coloumns - seperate after zip code

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions