Split data around a carriage return

[xp]

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!

 

[Lars-Åke Aspelin]

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

 

[Gord Dibben]

Data>Text to Columns>Delimited by>Other hit CTRL + j and Finish


Gord Dibben MS Excel MVP

 

[Peter T]

Might want to use CHAR(10) instead of 13

Regards,
Peter T

 

[Jeff]

Data -->Text to columns maybe?

 

[xp]

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

Any other ideas?

 

[Matthew Herbert]

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]

Nice trick! Thanks Gord!

Data>Text to Columns>Delimited by>Other hit CTRL + j and Finish


Gord Dibben MS Excel MVP



.

 

[xp]

Hi,

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

 

[xp]

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

Might want to use CHAR(10) instead of 13

Regards,
Peter T





.

 

[Ron Rosenfeld]

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