rows to columns

[yaro137]

I'm writing here as I couldn't find any way of achieving what I need
using Excels built-in modules so probably the only way is through some
code. I have a list of addresses that were imported to Excel and they
are all in one column. I need to get them to be in multiple columns.
E.g. I have 2 or 3 lines of one address in one cell so here I would
need to get each line in a separate column which would give 3 columns
with addresses and then underneath another cells with bits of address
and so on . Hope it makes sense. Can someone help me with this please?
yaro

 

[Per Jessen]

Hi

No code needed.
Look at "TextToColumns" on the Data menu.
To use the function first select the column, then follow the instructions.

Hopes this helps.

 

[yaro137]

Hi

No code needed.
Look at "TextToColumns" on the Data menu.
To use the function first select the column, then follow the instructions..

Hopes this helps.

I tried that but it won't do what I need unless you know what's the
code for end of line which I don't.
yaro

 

[Per Jessen]

Post an sample of your data, so we know what to deal with.

---
Per

"yaro137" <[email protected]> skrev i meddelelsen

Hi

No code needed.
Look at "TextToColumns" on the Data menu.
To use the function first select the column, then follow the instructions.

Hopes this helps.

I tried that but it won't do what I need unless you know what's the
code for end of line which I don't.
yaro

 

[yaro137]

Post an sample of your data, so we know what to deal with.

Not sure how to post it here but I've got something that looks like
this:

________A_______________ B
¦
The Long House ¦
8 Cavendish Square ¦
Barnet ¦
_________________ ¦_______
¦
33 Kings Road ¦
High Road ¦
Putney ¦
_________________ ¦_______
¦
9 Sesame Street ¦
Kent ¦
_________________ ¦_____
.....

and I need this:

_______A______________________B______________C________

The Long House ¦ 8 Cavendish Square ¦
Barnet ¦
_______________________________________________________

33 Kings Road ¦ High Road
¦ Putney ¦
_______________________________________________________

9 Sesame Street ¦
Kent ¦ ¦

Hope it displays fine. Thanks
yaro

 

[yaro137]

Well it didn't display right. I'll try without lines so it should be:

A
B C
The Long House 8 Cavendish Square Barnet
33 Kings Road High Road Putney
9 Sesame Street Kent

Hope this is clearer.
yaro

 

[Per Jessen]

Hi

As there are no delemitters in the string we have to insert some.

Select the cells, goto Edit > Find > Find what: " " (enter two spaces) >
Replace > Replace with ", " > Replace All.

Now use TextToColumns with the comma as delemiter"

Hopes this helps

 

[yaro137]

Hi

As there are no delemitters in the string we have to insert some.

Select the cells, goto Edit > Find > Find what: "  " (enter two spaces)>
Replace > Replace with ", " > Replace All.

Now use TextToColumns with the comma as delemiter"

Hopes this helps

It works all right with one space but not with 2 or more. Thanks
yaro