Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Delimited text by carriage return?

Hi,

I've got a CSV file, one of the fields contains a huge amount of text and I need to break it into columns. The only thing that's seperating the text into manageable sections is carriage returns.

Does anyone know a way of specifiying this as a delimiter?

Cheers in advance for your help,

Bobwhosmiles


Post your answer or comment

comments powered by Disqus
I have a column where each cell has values that are separated by a carriage
return, and I want to do a Text to Column action delimited by the carriage
returns. What is the best way to do this?

I have data in a single cell seperated by carriage returns. EG:

THREE WAY GARAGE
HOLMES CHAPEL ROAD
LACH DENNIS
NORTHWICH
CW9 7SZ

I want to replace the carriage returns with commas so that I can do a text to columns change using the comma to return the following:

THREE WAY GARAGE,HOLMES CHAPEL ROAD,LACH DENNIS,NORTHWICH,CW9 7SZ

Does anyone have any idea how I would go about this?

Thanks for your help!!

I have been trying to figure this out and just can’t seem to get it. I am hoping someone out there can help.

I have 4 cells with text in them that I am trying to combine into one cell and not have any blank lines between the text.

The cells are arranged like this:
A1="One" B1=Cell where the text is combined
A2="Two"
A3="Three"
A4="Four"

I set the cell alignment in B1 to wrap text and use the following formula in B1:
=IF(A1=””,””,A1)&IF(A2=””,””,CHAR(10)&A2)&IF(A3=””,””,CHAR(10)&A3)&IF(A4=””,””,CHAR(10)&A4)

The problem is if there isn’t anything in one of the cells in column A it makes a blank line between the text in column B. I am trying to get the text to the top of B1 and not have any blank lines between the lines of text.

Is there a way to change the formula so that it will do the following three things:
1. If the cell A1 doesn’t have anything in it then don’t put the value in B1.
2. If A1 has something in it and A2 has something in it then put the text of A1 in B1 and HAVE a carriage return after the A1 text.
3. If A1 has something in it and A2 does not have anything in it then put the text of A1 in B1 but DO NOT have a carriage return after it.

Thank you for your help in advance.

I have an Excel file that includes text with carriage returns inside the field. I personally achieve this by typing . I received a client's file that is full of text which I need to import into MS SQL. The text once in SQL will be displayed in an HTML page.

The Problem:
I need to replace the carriage returns inside the cells to
. The replace function in Excel cannot recognize the carriage return.

I tried entering the paragraph mark ¶ into the Replace dialog by typing on my keypad. Although the character was entered, Excel could not find that character. I can't find the alt code for the line feed.

Hi,

A cell contains the name, tel number and address of a user. Those 3 fields are separated by a carriage return (alt+enter) in the cell. I need a macro to keep only the name, which is on the first line of the cell. The macro should be generic and should work for any particular name.

Ex:

John Smith
96783456
Hollywood Street

The result should only give me:

John Smith

Can someone help me?

Separating carriage return data into separate cells

I imported data from a SQL database that has information stored in the cell
as information separated by carriage returns.

Keep in mind what is below is all on one cell:

This row will be random text and random length but there is information that
is valuable to me in this line of text
Name:JOE,SMITH
OrderNo:99999999
TotalSubmittedAmt:$999.99
PaidAmt:$99.99
ClaimDate:99/99/9999

I need to separate these fields into separate cells so I can use the data.
On the bottom 4 lines I know that the text will be identical but the first
line of text will vary depending. The results that I am looking for are
something like the following.

Text Name OrderNo TotalSubmittedAmt PaidAmt Date
First line JOE,SMITH 99999999 999.99 99.99 99/99/9999

Sorry for the size of this file. I made it as small a sample as I thought would show my problem.

I'm having a problem. If you look at the "export" sheet in the attached spreadsheet you will notice 6 rows of data. Behind the scenes each cell on this sheet has a "if" statement. The if statement is trying to only display those records that meet a certain criteria based on data it finds on the "data" sheet.

Visually this sheet looks like if you save this sheet as text you will end up with a text file that has 6 rows in it. However when you do this you end up with 6 records and 94 rows showing as just carriage returns. These extra rows are the hidden (should be blank) rows created from the If statement in those rows.

How can I construct a if statement so when I save this sheet as a text file only the "visible" rows get exported.

In normal use data starts at the first row and goes down to some point and then from there it's "blank". I will not be dealing with random blocks of data with blanks in between.

Thanks in advance.

hi there, am trying to split cells that have an address in them which is separated by carriage returns (i.e. address1, address2, town, county. I'm more than happy to convert the cells to comma separated values but am struggling to know how to do it. The cells (and there are plenty of them) vary in length as each address is different. Look forward to hearing from you soon.......

Regards,

Phil

_________________
Philip Lawrence

[ This Message was edited by: PhilipLawrence99 on 2002-11-11 11:48 ]

I've got a sheet with 2 columns - column A has multiple values in one cell
that are separated by carriage returns, and column B next to it has only one
value per cell.

I'd like to not only strip out these carriage returns, but also (the fun
part) add rows for each value and maintain the reference to the column next
to it.

Example:

A1 looks like this:

Milk
Eggs
Juice

B1 looks like this:

Food

I'd like A1 to contain the value "Milk" and B1 to contain "Food". A2 should
contain "Eggs" and B2 should contain "Food". C1 should say "Juice", and C2
should say "Food". You get the idea.

Thanks,

Adin

I have a spreadsheet that is used for construction change orders. The way it has to be set up there is 4 columns description, qty,cost per, and total. The description qty and cost per are all being pulled from another sheet in the work book and to create seperate line items I just use the carriage return feature on the other sheet. I need to get my total column to take and multiply qty x cost and enter it into total, then take the next carriage returned value and go quanity x cost and then move onto next carriage returned item. at the bottom of the totals column i will need a formula that will add up all the totals as well.

Thanks in advance for any help!

I have rows of data in one column. I want to combine them all into one cell and have each row on a new line.

I've tried =A1 & char(10) & A2, (and a bunch of other char(#s) for carriage return) but it just shows an open box in place of the char().

I hope I can do this w/o VB. Help?

I've got a sheet with 2 columns - column A has multiple values in one cell
that are separated by carriage returns, and column B next to it has only one
value per cell.

I'd like to not only strip out these carriage returns, but also (the fun
part) add rows for each value and maintain the reference to the column next
to it.

Example:

A1 looks like this:

Milk
Eggs
Juice

B1 looks like this:

Food

I'd like A1 to contain the value "Milk" and B1 to contain "Food". A2 should
contain "Eggs" and B2 should contain "Food". C1 should say "Juice", and C2
should say "Food". You get the idea.

Thanks,

Adin

I am trying to import a text file in which the user has entered carriage
returns for a field. When I import it into Excel, Excel is considering the
carriage return as a row delimiter. I am creating this text file from a .NET
program and would like that program to have to enter whatever special
characters Excel might need to interpret these returns as and a
newline in a cell and not as a new row. Thanks for your help.

EDIT: Solved

Fixed it by adding a semi-colon to the end of the print. Seems Print automatically inserts a return carriage


	VB:
	
 
Sub DeleteBreaks() 
     
    Dim str As Variant 
     
     
    Open "C:Testingtest.txt" For Input As #1 
    Open "C:TestingOutbreak.txt" For Output As #2 
     
    Do Until EOF(1) 
         
        Line Input #1, str 
         
        str = Replace(str, Chr(13), "", 1) 
        Print #2, str; " "; 
         
    Loop 
     
    Close 1 
    Close 2 
     
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


In order to later import a text file into excel and format it correctly as a CSV, I need to loop through and replace all the carriage returns denoting a new line in the text file. I would like to replace them with a space since I would like to have a single line instead of a multi-line document.

I was thinking of something along the lines of; however, I do not know how I would use the replace:


	VB:
	
 
Sub Import() 
     
    Open "c:File.txt" For Input As #1 
    Open "c:Output.txt" For Output As #2 
     
    Do Until EOF(1) 
         
        Line Input #1, str 
         
        Replace(??????????) ' Unsure what code should go here
         
    Loop 
     
    Close 1 
    Close 2 
     
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Any suggestions? Auto Merged Post;

Bump for found solution

I have a text file that has been exported from a database that contains multiple records. I would like to bring it into excel, but when I try to use the standard import features it doesn't work correctly. I believe the issue is associated with the use of carriage returns as record delimiters, but beyond that I'm not sure. Does anyone have any ideas? I've included an example of the text file for anyone who might want to help me out.

http://www.whatscookin.com/text_to_excel_sample.txt

Thanks!

I need to import a text file of data where the delimiter between fields is a
carriage return.
--
Gordo T

Hi. I have written a php script that takes results from a mySQL database and exports them to a text file where columns are delimited by t and rows are delimited by n. In some cases, the contents of a single cell are lists. I need a way to insert these where each item is on its own line within the cell. Effectively, I need it to look like there was an Alt+Enter character between each. I tried inserting the a carriage return characer, a paragraph character, etc. Nothing has worked. Any suggestions?

Thanks.

I'm trying to import a text file into excel. The file is tab delimited and uses double quotations as a text qualifier. One of the fields in the text file is a note field and every now and then the person entering the note uses a carriage return (hits the enter key) while doing so. When this comes through in the text file it looks like this...

"this is a note.
this is the second line of the note"

My understanding is that everything between the two instances of text qualifers should be treated as a single value and the carriage return disregarded. However when I import it the data comes in exactly as it is above which causes field types and column headings to not match up.

Can anyone tell me a way around this short of manually deleting each carriage return?

Hi all,

I have a dilemma with an exported .txt file that contains text with embedded carriage returns. When viewed in Excel, these display with a tiny [?] symbol. While in Excel 2010, I can get rid of these simply by highlighting the column and selecting 'Word Wrap'. The carriage return remains but the character is not displayed. However, users of Excel 2007 do not have the same experience. The 'word wrap' functionality does not hide the symbol after the text is wrapping within the cell. I have been able to use the =Clean(My_Column#) formula to remove these characters but

Is there a difference between the display of these characters between 2007 and 2010 excel versions? I don't want to send off reports to users using 2007 and have these symbols appear since I don't see them in 2010. I also prefer not to use the formula each time as well but i'm not sure that is possible

Attached is a screen capture of the symbol as it appears without word wrap in 2010.

Excel 2010.jpg

Hiya,
I searched your site high and low and could find plenty of ideas on how to remove a carriage return from a cell within Excel or how to remove them upon importing a text file into Excel, but could find nothing for the reverse. I found the solution elsewhere and thought I'd share the results.

I have an Excel file that I needed to be able to save rows of data into a text file. I was able to find a solution for copying the ros from Excel into text in this thread:
http://www.ozgrid.com/forum/showthread.php?t=47333

However, the above solution left a carriage return at the end of my txt file & the program I loaded the txt file into would not handle the file correctly without manually removing the carriage return and saving.

I found the following solution at:
http://www.microsoft.com/technet/scr...5/hey0520.mspx


	VB:
	
 ForReading = 1 
Const ForWriting = 2 
 
Set objFSO = CreateObject("Scripting.FileSystemObject") 
 
Set objFile = objFSO.OpenTextFile(myFile, ForReading) 
strFile = objFile.ReadAll 
objFile.Close 
 
intLength = Len(strFile) 
strEnd = Right(strFile, 2) 
 
If strEnd = vbCrLf Then 
    strFile = Left(strFile, intLength - 2) 
    Set objFile = objFSO.OpenTextFile(myFile, ForWriting) 
    objFile.Write strFile 
    objFile.Close 
End If 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Good Morning All,

I have been using Chr(10) to insert carriage returns in strings which I can then display in text boxes on a userform. Unfortunately since I've been upgraded to XP I no longer have the ActiveX add-in which my PC tells me is the missing library.

Does anyone know how to insert carriage returns without using Chr ?

Thanks

John

Hi

I have written a VBA concatenate function (see below), which takes a range, concatenates all the strings, splitting each one with a carriage return. The only problem is, that when this displays in excel, each of the vbCr characters displays as a little rectange, instead of a "new line" (i.e. ALT>ENTER). Is there anyway I can get around this?

Public Function afn_CONCATENATEWITHIN(r As Range) As String
Dim sTemp As String
Dim i As Long, j As Long

For i = 1 To r.Columns.count
For j = 2 To r.Rows.count - 1
If Len(r.Cells(j, i).Text) > 0 Then
If Len(sTemp) > 0 Then
sTemp = sTemp & vbCr & r.Cells(j, i).Text
Else
sTemp = r.Cells(j, i).Text
End If
End If
Next j
Next i
afn_CONCATENATEWITHIN = sTemp
End Function

Any help will be greatly appreciated.

Hello.

Quick poser for you here.

I'm putting loads of text into a spreadsheet and every time I put a carriage return (Alt+Enter) into a cell, it appears to be switching on Wrap Text. Problem is, this is making my rows fill half the screen!

Anyone know how to disable this? It's driving me quite potty.

Many thanks to all you lovely people. I'm not worthy.

Hi All,
In the past everyone has been very fast and helpful with my problems so thanks for that!

I have a new issue which google can't seem to answer, I have a Multi-line text box in a form which populates a merged will with its content, however it also adds the Carriage Return symbol (Square) into the cell, can anyone tell me how to fix this to stop it from being entered, or how I would go about parsing the entry of that particular symbol?

Thanks,
Chris


No luck finding an answer? You could always try Google.