Values in rows to seperate columns

[aethsilgne]

Hello! Sorry for asking if this has been posted elsewhere, couldn't find it..

I'm trying to have values in a row 'broken up' and be put in separate columns.

Example:

A1: 'Name Surname'
A2: 'street 21, 1111 AA City, Country'
A3: '+11 111 111 111 ‎ · website.eu'

A4 starts the same info again but with different values, and there are like a hundred of these.

Should become:
A1: 'Name Surname'
B1: 'street 21'
C1: '1111 AA'
D1: 'City'
E1: 'Country'
F1: '+11 111 111 111'
G1: 'website.eu'

Then the next range of values (which start in A4):

A2: 'Name Surname'
B2 'street 21'
C2: '1111 AA'
D2: 'City'
E2: 'Country'
F2: '+11 111 111 111'
G2: 'website.eu'

... repeat.

If anyone would know how to do this, they would be my new hero!

Thanks

Aeth

[AlphaFrog]

Try this...

Please Login or Register  to view this content.

[aethsilgne]

:o Great! Thanks!
The only value I still need split is '1111 AA City' into '1111 AA' and 'city'

I would like to try and change it myself but I can't actually 'read' the code

[AlphaFrog]

:o Great! Thanks!
The only value I still need split is '1111 AA City' into '1111 AA' and 'city'

I would like to try and change it myself but I can't actually 'read' the code

What's the criteria on where to split that text? I'm assuming the City could be more than one word e.g. "Los Angeles"?
Is 1111 AA always the first seven characters (including space)? Or maybe split at the second space character?

[aethsilgne]

The code that works brilliantly is:

Please Login or Register  to view this content.

The only extra line of code I could really use is the above code:

+ splitting '1111 AA City' into '1111 AA' & 'city' - the first seven digits always consist of '4 random numbers' *space* 'two random letters'

A different version of the same code that splits:
'11111 city' into '11111' & 'city' - the first five digits always consist of 5 random numbers

Another different version of the same code that splits:
'1111 city' into '1111' & 'city' - the first four digits always consists of 4 random numbers.

Another that splits:
'111 11 city' into '111 11' & 'city' - the first 7 characters always consists of '3 random numbers' *space* 'two random numbers'

Basically it's for 4 different excel sheets; One for Netherlands, Sweden, Finland and Denmark. They all have different postcodes unfortunately

A1 = ('thanks' * 100000)

[AlphaFrog]

You're welcome. No need to PM me. I automatically get notified when you reply to this thread.

This prompts the user to select the Country Postal Code for the sheet.

Please Login or Register  to view this content.

[aethsilgne]

Amazing stuff! Thanks a million!