Split a text
 

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

Split a text
 

Hi
I don't think this can be down with DataText 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 SpecialValues back over the formulae to fix
the data.
--
Regards
Roger Govier



"Manos" wrote in message
...
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

Split a text
 

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.
--
Cheers

Rodrick


"Manos" wrote:

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

Split a text
 

On Wed, 17 Oct 2007 04:25:01 -0700, Manos
wrote:

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

Split a text
 

Thank you Both

"Roger Govier" wrote:

Hi
I don't think this can be down with DataText 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 SpecialValues back over the formulae to fix
the data.
--
Regards
Roger Govier



"Manos" wrote in message
...
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

Split a text
 

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.

--
Regards
Roger Govier



"Ron Rosenfeld" wrote in message
...
On Wed, 17 Oct 2007 04:25:01 -0700, Manos

wrote:

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

Split a text
 

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


Gord Dibben MS Excel MVP

On Wed, 17 Oct 2007 13:10:23 +0100, "Roger Govier"
<rogerattechnology4NOSPAMu.co.uk wrote:

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.

Split a text
 

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

--
Regards
Roger Govier



"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
CTRL + j also works in the "replace what" dialog to locate the linefeed
Alt +
Enter


Gord Dibben MS Excel MVP

On Wed, 17 Oct 2007 13:10:23 +0100, "Roger Govier"
<rogerattechnology4NOSPAMu.co.uk wrote:

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.