i'm trying to split data which is seperated by line breaks into seperate
columns. line breaks are not in the standard delimiter list and i cant find a
way or doing this.

ie
John Smith
23 Tree Terrace
London
SW3
email
tel 01483 522563


any suggestions please! thanks

--
Jackie Pearce

Jackie Pearce wrote:
i'm trying to split data which is seperated by line breaks into seperate
columns. line breaks are not in the standard delimiter list and i cant find a
way or doing this.

ie
John Smith
23 Tree Terrace
London
SW3
email
tel 01483 522563


any suggestions please! thanks


Assuming your data starts at A1, put the following formula in B1:

=SUBSTITUTE(A1," ","~")

In between the empty quotes, instead of a space hit Alt-Enter.

Copy the formula down to match your list of data. Then select all of the
formulas and Copy, then Paste / Special / Values.

Now do a Text To Columns using "~" as the delimiter.

thanks all, very helpful and a great time saver!

J
--
Jackie Pearce



"Glenn" wrote:

Jackie Pearce wrote:
i'm trying to split data which is seperated by line breaks into seperate
columns. line breaks are not in the standard delimiter list and i cant find a
way or doing this.

ie
John Smith
23 Tree Terrace
London
SW3
email
tel 01483 522563


any suggestions please! thanks


Assuming your data starts at A1, put the following formula in B1:

=SUBSTITUTE(A1," ","~")

In between the empty quotes, instead of a space hit Alt-Enter.

Copy the formula down to match your list of data. Then select all of the
formulas and Copy, then Paste / Special / Values.

Now do a Text To Columns using "~" as the delimiter.

You might try replacing your line breaks (assuming that they are in the form
of alt+enter) with a character such as "~" or "`".
=SUBSTITUTE(A1,CHAR(10),"~")

Once you've done that, copy that result and PasteSpecial, values only. Then
you can use Text to Columns using "~" as the delimeter.

HTH,
Paul

--

"Jackie Pearce" wrote in message
...
i'm trying to split data which is seperated by line breaks into seperate
columns. line breaks are not in the standard delimiter list and i cant
find a
way or doing this.

ie
John Smith
23 Tree Terrace
London
SW3
email
tel 01483 522563


any suggestions please! thanks

--
Jackie Pearce

DataText to ColumnsDelimited byOther........CTRL + j and OK


Gord Dibben MS Excel MVP

On Fri, 22 Aug 2008 05:50:00 -0700, Jackie Pearce
wrote:

i'm trying to split data which is seperated by line breaks into seperate
columns. line breaks are not in the standard delimiter list and i cant find a
way or doing this.

ie
John Smith
23 Tree Terrace
London
SW3
email
tel 01483 522563


any suggestions please! thanks

Hi Jackie,

To split data where line breaks are the delimiter, you can use the Text to Columns feature in Excel. Here are the steps:

1. Select the cell or range of cells that contain the data you want to split.
2. Click on the Data tab in the ribbon.
3. Click on the Text to Columns button in the Data Tools group.
4. In the Convert Text to Columns Wizard, select Delimited and click Next.
5. Uncheck all the delimiter options and check Other.
6. In the box next to Other, press Ctrl + J. This will enter a line break as the delimiter.
7. Click Next.
8. Choose the data format for each column. For example, you can choose Text for all columns.
9. Click Finish.

This will split the data into separate columns based on the line breaks. You may need to adjust the column widths and formatting to make the data easier to read.