Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! . |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Separating carriage return data into separate cells | Excel Discussion (Misc queries) | |||
Data import, carriage return | Excel Programming | |||
Data import, carriage return | Excel Programming | |||
Carriage return symbol - how to split columns at | Excel Discussion (Misc queries) | |||
Importing data to excel (newline/carriage return issue) | Excel Discussion (Misc queries) |