Split data around a carriage return
 

I have a column of data. In each row, is carriage return character in the
middle of some data. The carriage return is never in the same position and
the text is different lengths on each row.

I need to split this data into two columns with the data to the left of the
carriage return in one column and the data to the right of the carriage
return in another column and leave out the carriage return altogether.

Can anyone help me out with a solution to this? I can code or use formulas
or even a built in command if there is one...thanks!

Split data around a carriage return
 

On Fri, 22 Jan 2010 13:10:04 -0800, xp
wrote:

I have a column of data. In each row, is carriage return character in the
middle of some data. The carriage return is never in the same position and
the text is different lengths on each row.

I need to split this data into two columns with the data to the left of the
carriage return in one column and the data to the right of the carriage
return in another column and leave out the carriage return altogether.

Can anyone help me out with a solution to this? I can code or use formulas
or even a built in command if there is one...thanks!

If your data is in column A starting on row 1, try this:

In cell B1:
=LEFT(A1,FIND(CHAR(13),A1)-1)

In cell C1:
=RIGHT(A1,LEN(A1)-FIND(CHAR(13),A1))

Copy cells B1:C1 down as far as you have data in column A.

Hope this helps / Lars-Åke

Split data around a carriage return
 

DataText to ColumnsDelimited byOther hit CTRL + j and Finish


Gord Dibben MS Excel MVP

On Fri, 22 Jan 2010 13:10:04 -0800, xp wrote:

I have a column of data. In each row, is carriage return character in the
middle of some data. The carriage return is never in the same position and
the text is different lengths on each row.

I need to split this data into two columns with the data to the left of the
carriage return in one column and the data to the right of the carriage
return in another column and leave out the carriage return altogether.

Can anyone help me out with a solution to this? I can code or use formulas
or even a built in command if there is one...thanks!

Split data around a carriage return
 

Might want to use CHAR(10) instead of 13

Regards,
Peter T


"Lars-Åke Aspelin" wrote in message
...
On Fri, 22 Jan 2010 13:10:04 -0800, xp
wrote:

I have a column of data. In each row, is carriage return character in the
middle of some data. The carriage return is never in the same position and
the text is different lengths on each row.

I need to split this data into two columns with the data to the left of
the
carriage return in one column and the data to the right of the carriage
return in another column and leave out the carriage return altogether.

Can anyone help me out with a solution to this? I can code or use formulas
or even a built in command if there is one...thanks!

If your data is in column A starting on row 1, try this:

In cell B1:
=LEFT(A1,FIND(CHAR(13),A1)-1)

In cell C1:
=RIGHT(A1,LEN(A1)-FIND(CHAR(13),A1))

Copy cells B1:C1 down as far as you have data in column A.

Hope this helps / Lars-Åke

Split data around a carriage return
 

Data --Text to columns maybe?

"xp" wrote:

I have a column of data. In each row, is carriage return character in the
middle of some data. The carriage return is never in the same position and
the text is different lengths on each row.

I need to split this data into two columns with the data to the left of the
carriage return in one column and the data to the right of the carriage
return in another column and leave out the carriage return altogether.

Can anyone help me out with a solution to this? I can code or use formulas
or even a built in command if there is one...thanks!

Split data around a carriage return
 

No...I already tried that...it won't recognize the carriage return to break
on...

Any other ideas?


"Jeff" wrote:

Data --Text to columns maybe?

"xp" wrote:

I have a column of data. In each row, is carriage return character in the
middle of some data. The carriage return is never in the same position and
the text is different lengths on each row.

I need to split this data into two columns with the data to the left of the
carriage return in one column and the data to the right of the carriage
return in another column and leave out the carriage return altogether.

Can anyone help me out with a solution to this? I can code or use formulas
or even a built in command if there is one...thanks!

Split data around a carriage return
 

Xp,

The code below should give you enough code to play around with so that you
can customize it to your situation.

Best,

Matthew Herbert

Sub Testing123()
Dim strSep As String
Dim rngSplit As Range

'Chr(13) or vbCr is the carriage return
'see "Miscellaneous Constants" in VBE Help for more options
strSep = Chr(10)

Set rngSplit = Worksheets(1).Range("A1")

rngSplit.TextToColumns Other:=True, OtherChar:=strSep

End Sub


"xp" wrote:

I have a column of data. In each row, is carriage return character in the
middle of some data. The carriage return is never in the same position and
the text is different lengths on each row.

I need to split this data into two columns with the data to the left of the
carriage return in one column and the data to the right of the carriage
return in another column and leave out the carriage return altogether.

Can anyone help me out with a solution to this? I can code or use formulas
or even a built in command if there is one...thanks!

Split data around a carriage return
 

Nice trick! Thanks Gord!

"Gord Dibben" wrote:

DataText to ColumnsDelimited byOther hit CTRL + j and Finish


Gord Dibben MS Excel MVP

On Fri, 22 Jan 2010 13:10:04 -0800, xp wrote:

I have a column of data. In each row, is carriage return character in the
middle of some data. The carriage return is never in the same position and
the text is different lengths on each row.

I need to split this data into two columns with the data to the left of the
carriage return in one column and the data to the right of the carriage
return in another column and leave out the carriage return altogether.

Can anyone help me out with a solution to this? I can code or use formulas
or even a built in command if there is one...thanks!

.

Split data around a carriage return
 

Hi,

Thanks for this approach...unfortunately I get "#Value!" error...



"Lars-Ã…ke Aspelin" wrote:

On Fri, 22 Jan 2010 13:10:04 -0800, xp
wrote:

I have a column of data. In each row, is carriage return character in the
middle of some data. The carriage return is never in the same position and
the text is different lengths on each row.

I need to split this data into two columns with the data to the left of the
carriage return in one column and the data to the right of the carriage
return in another column and leave out the carriage return altogether.

Can anyone help me out with a solution to this? I can code or use formulas
or even a built in command if there is one...thanks!

If your data is in column A starting on row 1, try this:

In cell B1:
=LEFT(A1,FIND(CHAR(13),A1)-1)

In cell C1:
=RIGHT(A1,LEN(A1)-FIND(CHAR(13),A1))

Copy cells B1:C1 down as far as you have data in column A.

Hope this helps / Lars-Ã…ke
.

Split data around a carriage return
 

I get a value error with either 10 or 13...

"Peter T" wrote:

Might want to use CHAR(10) instead of 13

Regards,
Peter T


"Lars-Ã…ke Aspelin" wrote in message
...
On Fri, 22 Jan 2010 13:10:04 -0800, xp
wrote:

I have a column of data. In each row, is carriage return character in the
middle of some data. The carriage return is never in the same position and
the text is different lengths on each row.

I need to split this data into two columns with the data to the left of
the
carriage return in one column and the data to the right of the carriage
return in another column and leave out the carriage return altogether.

Can anyone help me out with a solution to this? I can code or use formulas
or even a built in command if there is one...thanks!

If your data is in column A starting on row 1, try this:

In cell B1:
=LEFT(A1,FIND(CHAR(13),A1)-1)

In cell C1:
=RIGHT(A1,LEN(A1)-FIND(CHAR(13),A1))

Copy cells B1:C1 down as far as you have data in column A.

Hope this helps / Lars-Ã…ke


.

Split data around a carriage return
 

On Fri, 22 Jan 2010 13:55:01 -0800, xp wrote:

No...I already tried that...it won't recognize the carriage return to break
on...

Any other ideas?

If it does not recognize what you are entering as a carriage return, then you
are not entering the same character(s) that are in your string.

Pick your string apart with code like:

A1: Sample string
B1: =MID($A$1,COLUMNS($A:A),1)
B2: =CODE(B1)

Select B1:B2 and fill right until you are into the second line. The code or
codes in between should tell you what your system is using for a carriage
return.
--ron