Free Microsoft Excel 2013 Quick Reference

Carriage Returns

Hi all

I have done a search on carriage returns/characters and what has been suggested is not working.

I have BOTH carriage returns in the cell and also the boxes (that I assume are also carriage returns.

I tried the substitute formula (=substitute(cellref,char(10),)) in excel but it only removes the alt+enter carriage return. I tried the various suggestions of find & replace but the chr$..etc did not work!

Is there a formula to clear both in one (not a macro!)..?

I attach an example. Any help really appreciated.

dugong


Post your answer or comment

comments powered by Disqus
Hi folks, below is code to import a text file to a string-variable, undertake an operation, then save this text file.
Problem: an carriage-return is added to the end of the text file when saving back to the text file - how do I stop this from happening?

(The original text file that is imported has one carriage return at the end of the file; after applying this code, the text-file has two carriage returns at the end of the file.)


	VB:
	
 Change_Filename_In_HeaderRow(fullpath_fn) 
    Dim txt As String, fullpath_fn As String 
     
    txt = CreateObject("Scripting.FilesystemObject").OpenTextfile(fullpath_fn).ReadAll 
    txt = Replace(txt, "blah1", "blah2") 
     
    Open fullpath_fn For Output As #1 
    Print #1, txt 
    Close #1 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Many thanks, Peter.

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

Hey all,

I am exporting data from a system that I can not change. The data has these headings Company, First Name, Last Name, Date, Amount, Comments, Local Amount, Exchange Rate, Heading 1, Heading 2.

The data when excluding comments column, comes out great, with each piece of information is on its own row.

If comments column is exported with the rest of the data, than excel imports that data splitting SOME of the comments column (which i believe is a carriage return in the original source data), into a new line beginning in column A which is the Company Column. How do i move everything back into one line? If possible I would like the carriage return data to be in the Comments column as a carriage return WITHIN the cell itself.

I can export the data choosing my own delimiter character, which i use the "^" symbol.

Please advise.

I have attached a sample excel sheet to explain my issue.

I have approx. 5000 csv files to import onto a single page.
They all have the same format.
When I open the files with excel the data parses properly but the titles don't with the result that the last column of data has no header. If you open one of the files you will see what I mean.
When I tried to record a macro of the import it did not work because there are carriage returns in the last column of data which causes the data to wrap around so the columns don't line up and I don't know how to get around that using the import tool.
The following code I found in another thread does much of what I want but needs adjusting as the records end up split over two rows so it needs to be adjusted.

	VB:
	
 import_fresh_txt_files() 
    Dim myDir As String, fn As String, txt As String, size As Integer, x 
    myDir = "c:test" '

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


Hello,
I am trying to set excel to include carriage returns in a cell. The cell contents are intended to be copied for use outside excel.

I have noticed that every time I copy the cell in concern, I get a quotation marks surrounding the text when I paste it in any text editors like notepad.

Is there a way I could get rid of this annoying addition, the quotes?

I am using the following vba to enforce a carriage return in a cell:


	VB:
	
 Range) 
    Sheet1.Range("A1") = "First Line" _ 
    & vbCrLf & "second Line" 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I did intially try to use char(10) in the formula in conjunction "wrap text" formatting but this was completely ignored when copied and pasted in notepad. I believe it is only a visual thing in excel.

Respectfully,
Ray

Hello,

I have written a program that will create and save a CSV file based on data entered into an Excel sheet. This program was written to be uploaded into another system by my client. The problem is that when Excel saves a CSV file, it inserts a carriage return at the end of every line, including the very last line and the system we are using will think this last carriage return is another line of data and will throw an error because it is blank. Is there anything I can put in my VBA code to automatically remove this last carriage return?

Thank you,
Lisa

Hi,

I have an SQL query that returns some text data to a cell from another system.
In this system the users sometimes enter superfluous carriage returns after the text.

Can someone tell me how to write a formula/VBA code that would remove the trailing carriage return/s.

Thanks.

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.

Hello all,
I am capturing a data stream as text and then PRINT ing to a text file.
However when I open the text file (after it has been filled with data) it contains an additional carriage return. Does anyone know why?

I have included the code. Has anyone encountered this issue in the past?

I would like to do something other than just get rid of the last carriage return.
But if someone is aware of a safe way to do that I would be very grateful.


	VB:
	
 
    Dim intInFile As Integer 
    Dim intOutFile As Integer 
    Dim strFileData As String 
     ' Create the output filename
    strOutFileName = GetNewFileName(strFileName) 
    strOutFileName = Left(strOutFileName, Len(strOutFileName) - 4) & ".txt" 
     
     ' Write the streamed file data to the output file
    intOutFile = FreeFile 
    Open (strOutFileName) For Output As #intOutFile 
    Print #intOutFile, strFileData 
    Stop 
     'At this point there is no additional line
    Close #intOutFile 
     'At this point there is an additional line
End Function 

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

I want to compose a formula that will result in not only combining some cells together but also will insert a carriage return in some spots. However, I don't know how to do this.

e.g.
If A1 = Joe Smith, A2 = President, A3 = ABC Company

I want to create a formula in A4 that concatenates the three cells and produces

Joe Smith
President
ABC Company

which I can later copy and paste into other applications to use.

What would that formula look like?

Hi all, I often receive data from clients that is in an Excel spreadsheet which contains lots of cells with carriage return characters (little lines represented by square boxes).
Now I know that cleaning the data removes them, as does the VBA code that uses the application.worksheet.clean function, but what I want to do is find all carriage returns and replace them with a comma.

Does anyone know of a way to do this??

Is there a way to use the vbCrLf code within perhaps a substitute or replace function?

I managed to achieve it by putting it all in Word and replacing all the ^p's, which did do the trick, the problem is, we clean data ready to run it through what is called a Mailsort programme for postage, this application throws out an extra few hundred records when I use the ^p method to remove carriage returns, where as if I remove them using the clean function in Excel it works perfectly but I need a way of removing them and replacing them with a comma or space as I don't want to join the parts of the string.
And the strings are always made up of varying parts, never something simple like forename, surname, but usually full addresses etc.

Can anyone help me?

Kind Regards
Boony

Hi all,

I'm not sure if this is possible, but can you put a carriage return or new line in a ControlTipText field?

If this is not possible, does anyone have any suggestion as to how I can add a mouseover text on a label where the text spans multiple lines?

Thanks for your help!

cheers,
Ganny

In Excel I have

B20: 200
B21: 200
B22: 201
B24: 201
B25: 206
B26: 201

The result I want in a file

200 200 201 201 206 201

---------------------
Right now my code look like this, but it prints a new line after each number


	VB:
	
Dim MyArray As Variant 
Dim iRow As Integer 
 
MyArray = Range("B20:B26").Value 
For iRow = 1 To 16 
    Print #iFileNum, MyArray(iRow, 1) 
Next 
 '*****************Print

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
How do you print them without the carriage returns? Any help is greatly appreceiated. Thanks.

Hi,

just starting with Access...can't seem to force a carriage return when entering text data into a field of type memo in a table...I know ALT-Enter works in spreadsheet cells in Excel but what do you use in Access?

thanks

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.

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

Is there anyway to use Find/Replace to delete all the carriage returns in a column of cells? Thanks in advance!

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.

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

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?

I would like to know if there's a quick and dirty way to strip the carriage return at the end of any cells that end with a carriage. Thanks in advance!

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.


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