Free Microsoft Excel 2013 Quick Reference

Removing carriage returns from excel

I have spent a good 8 hours on this over the last week and CANNOT figure this
out. I have exported all our contacts out of Outlook to excel and the
addresses and some other fields are being exported with the carriage return,
but you cannot see the symbol. If I export to tab delimited file, the ones
with the carriage return are being put into 2 to 3 separate lines and makes
importing impossible.

For example, when I click on a field it actually appears as

123 Street
Unit 4

So if it's exported to text, it messes up everything. I have tried every
formula I can possible try and downloaded an add-on to view code and it says
"cell has an apostrophe prefix" but cannot see that either.

I can't even import into access and try to export to text as it still
maintains this character somewhere.

Please can someone please tell me how to formute the cell as 123 Street Unit
4?


Post your answer or comment

comments powered by Disqus
Hi There. I'm trying to automatically remove carriage returns from an Excel
2000 Spreadsheet. I've tried the holddown alt and type 0010 or 0013 in the
find and replace, but without success.

Any advice would be greatly appreciated.

Thanks

I have found this code to be very helpful in removing carriage returns on worksheets designed for other people to complete.

Private Sub Worksheet_Change(ByVal Target As Range)

Target.Replace What:="" & Chr(10) & "", _
Replacement:=" ", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Target.Rows.AutoFit

End Sub

Happy VBing.
JoJo

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


I have an Excel spreadsheet saved from nn Access database that had carriage
returns in it. How can I now automatically remove these from Excel?
Search-and-replace does not work.

Thank you,

Mike O.

I have some data that I am manipulating in Excel to do an export to another
application. I need to be able to do a universal find and replace to get rid
of instances of Carriage Returns and Line Feeds within the data. I have
tried the basic find and replace functions but I don't know how to make them
find a carriage return character.

Any help would be greatly apreciated.

Hi,

I was hoping someone could point me in the right direction(s).
I have a column in my spreadsheet that contains "notes". I need to remove all the carriage returns from each note.

Any help would be greatly appreciated.

Thank you much in advance, Case

Hi.

I have been using the Replace function to remove unwanted characters from multiple spreadsheet entries.

For example:
name2 = Replace(name1, "-", " ")
(This replaces any hyphens in name1 with spaces.)

But some of the entries in my spreadsheet have carriage returns in some of the cells.
Is it possible to use the Replace function to remove these as well?
How is that done?

Or is there another way of removing unwanted carriage returns from cells?

Thanks in advance for your help.

I want to do a find and replace a space with a carriage return in excel and
am having problems finding a way to do this

To insert a carriage return in Excel, I press ALT+ENTER. How do I do this
when using "Find and Replace"?

I want to replace a given character (",") with a carriage return across a
large range of cells.

Hello everyone,

I have a worksheet containing square carriage return symbols (see below).

EXAMPLES
Eg1. Baked[]Beans on []Toast
Eg2. Smoked[][]Salmon in Brine
Eg3. Ice[][][] Cream [][]Cosmopolitan
Eg4. Mixed[]Nuts[][]per kilo[][][][]
Eg5. [][]Baby Shampoo[]Fragrance Free

This data was extracted from an SQL Database and dumped into Excel.

I would like to know if someone can suggest a script to replace all occurrences of [] with a space.

Once I perform this, I can then perform a Search/Replace function as follows:

Replace 4 spaces with 1 Space
Replace 3 spaces with 1 Space
Replace 2 spaces with 1 Space

Hopefully then, the final result will appear as follows:

Eg1. Baked Beans on Toast
Eg2. Smoked Salmon in Brine
Eg3. Ice Cream Cosmopolitan
Eg4. Mixed Nuts per kilo
Eg5. Baby Shampoo Fragrance Free

Eg5. is a little tricky because a space will appear at the beginning of the cell (see above). It would be good if there's a script to remove occurrences of this also.

I hope this makes sense.

I would appreciate ANY assistance with this....

With thanks,

NoniJones

I have some data which is importing in from an application called Oracle Discoverer into an Excel spreadsheet. It imports carriage returns symbols which appear as small boxes between text. I have tried Find and Replace to try and remove them but it does not recognize the carriage return symbol boxes.

Is some way of removing these symbols in a macro?

There are also duplicate rows, is there some way of removing these in a macro too

Thanks in advance

Des

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

I had a spreadsheet returned to from from a mail house b/c my address cell had a 'hard' carriage return in the cell. The text is not wrapped so unwrapping the text did not delete the carriage return. I know to get the carriage return, you use , but I can't seem to remove it.

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!

Hi all,

I need to find all <CR> character (carriage return) because when I export a table from Access to Excel, the data contains "[]" that are carriage returns.

so i need to find these characters and replace with null

can i do this with vba?

many thanks

I've been messing with a little project to remove extra carriage returns from reports at work. They show as that "box" character that you might see on the web when you don't have the proper font for whatever language a foreign site's written in. Well, I took my work home and I'm not seeing the boxes when there's a carriage return now.

Is this a setting I can toggle? Is it possibly related to the fact that I've got east asian language support installed at home, but not at work?

I'm looking at the exact same reports. My work machine is running Excel 2003 on Windows XP SP3. My home machine is running Excel 2003 on Windows XP 64-bit.

I am a Microsoft employee. The "Address" command from Excel 2003 was
extremely helpful for me to copy & send links to Microsoft counterparts that
have access to local server without sending big files by e-mail. The
"Location" in Excel 2007 does not provide full file path which is very
inconvenient; please do not remove a helpful feature that already exists in
Excel 2003! Thank you.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...et.f unctions

How do you insert a carriage return within an Excel cell?

Hi Guys,

I am pulling my hair out with this one! I have a CSV of data, this CSV has over 800,000 lines/rows so i cant do this manually.

Basically, look at the file I have uploaded, before the word "Barry" there is some kind of invisible return carriage going on! If you copy and paste the cell into another cell it appears the same, however double click the cell and copy the actual text and now paste it into another cell or microsoft word, the carriage return appears!!

I have a lot of data where I am copying columns into another program but it keeps recognising these carriage returns and it gives me more lines/rows than I should have!

Is there any way I can find these invisible carriage returns in excel and remove them?

Cheers,

Mike

Hi all, I would like to create xls file via code (C#), and I've thought to
create a simple html file renaming it to xls. That works, but I'm no able to
write return character in a single cell (Alt+Return). If I write 'html
return' (<br>), excel creates a new cell, but I would return to be in the
same cell!

Any suggestion?
Thanks a lot
Edo

I have exported an Outlook address book to a csv file & have opened the file
in Excel. The address field is a multiline field with Chr(10) designating a
new line. I am trying to convert from multiline to separate fields. Each
line is a new field. I am trying to replace the Chr (10) to a ~ then I can
format into separate fields using the Text to Columns function.

My problem is that I cannot see how to replace the Chr(10)

I have seen the following macro in a previous post which I modified but this
does not replace the Chr(10) but adds the ~ after the Chr(10)

Sub CharacterReturn()
'
' CharacterReturn Macro
'
'removes carriage returns from A1 down
Dim Rng, r As Range
Set Rng = Range(Cells(1, 1), _
Cells(ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row, 1))
For Each r In Rng
r.Value = Application.Substitute(Trim(CStr(r.Value)), Chr(10), "")
Next r

End Sub

Any help would be greatly appreciated. TIA
--
Tony

OK, so this sounds really stupid, but I can't figure out how to enter a carriage return in Excel! When you hit return, it goes to the next box-as we all know. But is there any way to enter a carriage return so you can go to the next line in a cell?
Thanks in advance!

Hi

We are having some strange problems with SQL statemnts when users want
to edit a database request in MS Query.

First the user go to "Import External Data - "New Data request" (I don't
know the exact name for this since I haven't got an English Excel at
hand), select the source and then get into the selection guide. Here the
user just select a table and exits and get into MS Query. He then add
a couple of criteria - a number "Greater Than xxx" and "Less than xxx".
He then returns the result to Excel and everything looks fine.
When he then go back and wants to edit the selection, he get and error
like "Incorrect syntax near 'Og'. Statement(s) could not be prepared".
When I then look at the SQL statement, I can see that it translates and
"And" og "og" (which is the danish word for And). Everything else is ok.

If I look at the SQL statement before I return to Excel it looks like this :

SELECT FinSelskab.FinSelskabNr, FinSelskab.FinSelskabNavn
FROM DRIFT.dbo.FinSelskab FinSelskab
WHERE (FinSelskab.FinSelskabNr>10000) AND (FinSelskab.FinSelskabNr<20000)

- which looks fine.

When I then return FROM Excel and get the error, the statement now looks
like this :

SELECT FinSelskab.FinSelskabNr, FinSelskab.FinSelskabNavn FROM
dbo.FinSelskab WHERE ((FinSelskab.FinSelskabNr>10000 Og <20000))

- and here it has translated "And" to "og".

We are runnning Excel in Citrix and there are a number of users who has
the problem, and at least one who doesn't. We've tried it on different
Citrix servers, but it doesn't looks like it's server related, but more
related to a user setting somewhere - maybe in the users profile.

I've looked high and low for a solution, but I'm starting to run out of
ideas.....

Regards
Steen

a week ago I asked an Excel programming question related to searching for a particular string. Leith Ross provided a solution based on RegExp. I am trying to use this method now for a PowerPoint macro.

I used a PowerPoint macro that I use to remove carriage returns from a textbox and merged in the RegExp code. The result can be seen below.

With this sub I am getting an Object required error message for the replace statement. I have no idea how to code this part correctly. Does anybody know how to debug this line.

Sub DeleteBracketsAndContents()
    Dim oshp As Shape
    Dim otxt As TextRange
     
    Set RegExp = CreateObject("VBScript.RegExp")
    RegExp.IgnoreCase = True
    RegExp.Pattern = "[[]number.*[]]"
     
     'check for type of selection 
     'replaces all text in shape unless text is highlighted
    If ActiveWindow.Selection.Type = ppSelectionNone _
    Or ActiveWindow.Selection.Type = ppSelectionSlides Then Exit Sub
    If ActiveWindow.Selection.Type = ppSelectionShapes Then
        Set oshp = ActiveWindow.Selection.ShapeRange(1)
        Set otxt = oshp.TextFrame.TextRange
    End If
    If ActiveWindow.Selection.Type = ppSelectionText Then
        Set otxt = ActiveWindow.Selection.TextRange
        If Len(otxt) < 1 Then
            Set otxt = otxt.Parent.Parent.TextFrame.TextRange
        End If
    End If
    
    'Replace text
    With otxt
              '    .Text = Replace(.Text, vbCr, " ")  
              '    The above line removes carriage returns in my PowerPoint macro
              '     I replaced vbCr with Reg.Pattern. It was a wild guess.
          .Text = Replace(.Text, Reg.Pattern, "")         
    End With
    
    Set RegExp = Nothing
    
End Sub



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