Split a text

[Guest]

Dear All

I have in column E mix address with post codes
eg: Blythswood square
G12BG Glasgow

In the text the split between address and post code is by Enter

How can i split it into two columns F & G
I wne form data --> text to column but i do not know how to declare the
Enter symbol.
Any ideas?

Thansk in advance
Manos

 

[Roger Govier]

Hi
I don't think this can be down with Data>Text to columns.

In cell B1 enter
=LEFT(A1,FIND(CHAR(10),A1)-1)
in cell C1 enter
=MID(A1,FIND(CHAR(10),A1)+1,255)
Copy down as far as required.

Then copy columns B:C and Paste Special>Values back over the formulae to fix
the data.

 

[Guest]

In column F enter this formula
=LEFT(A1,FIND("
",A1,1)-1)

In column G enter this formula

=LEFT(A1,FIND("
",A1,1)-1)

between quotation marks press ALT+ENTER.

 

[Ron Rosenfeld]

Dear All

I have in column E mix address with post codes
eg: Blythswood square
G12BG Glasgow

In the text the split between address and post code is by Enter

How can i split it into two columns F & G
I wne form data --> text to column but i do not know how to declare the
Enter symbol.
Any ideas?

Thansk in advance
Manos

To use the line feed symbol as the delimeter, when you get to the "delimiter"
page in the text-to-columns wizard, select Other. With the cursor in the box
next to other, hold down <alt> while you type ON THE NUMERIC KEYPAD "0 1 0"
(You must use the keypad, and not the numbers above the regular keyboard).

Then release the <alt> and select Finish.
--ron

 

[Guest]

Thank you Both

Hi
I don't think this can be down with Data>Text to columns.

In cell B1 enter
=LEFT(A1,FIND(CHAR(10),A1)-1)
in cell C1 enter
=MID(A1,FIND(CHAR(10),A1)+1,255)
Copy down as far as required.

Then copy columns B:C and Paste Special>Values back over the formulae to fix
the data.

 

[Roger Govier]

Excellent Ron!

I had tried typing Alt Enter, and Char(10) into the box - all to no avail.
Didn't think about using your method.

 

[Gord Dibben]

CTRL + j also works in the "replace what" dialog to locate the linefeed Alt +
Enter


Gord Dibben MS Excel MVP

 

[Roger Govier]

Thanks Gord.
That's two new things (at least) learned today!!!