Free Microsoft Excel 2013 Quick Reference

Carriage Returns in a Cell - what character?

Hi there

I was looking for a way to paste data from Word into Excel, and found several posts on the topic (e.g. or

However, I didn't find any explanation on what the ASCII character would be for a "soft" carriage return (i.e. the character entered via ALT-Return in Excel). It would be helpful for a search & replace routine.

Any ideas?

Post your answer or comment

comments powered by Disqus
I know that Alt-Enter inserts a carriage return in a cell, however, I need to
do this programatically, gathering text from cell one, inserting the carriage
return, then text from cell 2.

Cell A1 contains the text "LINE ONE "
Cell A2 contains the text "LINE TWO"

=A1&A2 will give you LINE ONE LINE TWO

But, what I need is:

Using Word Wrap is not an answer I can use, the formula needs to contain a
carriage return. Many thanks!

Hi all,

I am attempting to write a macro that will go through each cell within a given range and find the number of carraige returns in that cell. I have been able to find information regarding replacing special characters like carraige returns, but nothing on specifically identifying them. If anyone could give me some guidance that would be much appreciated.


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!

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.

Hi All,

Is it possible to have a carriage return in a text box on either a form or a report? I have a small report built into one textbox, but the names are of varying lengths and I would like a consistent look, with a few carriage returns in the text box.



Alt+enter to carriage return within a cell does not work...? Is there
something wrong with my computer or is there an option somewhere that could
be turned off?


I'm generating excel files using the perl module "Spreadsheet::WriteExcel". I'm trying to insert cells which contain a carriage return.

I'm using the newline character (n) in the perl script, but it seems not to work. Any idea ?


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?


hi guys,

I'm trying to format a comments column where there are too many blank lines that I want to eliminate by replacing instances of multiple carriage returns in the string with just one.

I am currently using:

Me.Cells(i, j) = WorksheetFunction.Substitute(Me.Cells(i, j), "&vbCrLf&vbCrLf&", vbCrLf)

I have no idea what the correct syntax would be to achieve this effect.

Any help would be appreciated!



Hey guys, I am trying to figure out how to change a certain character within a string in a cell. What I am currently using that seems to work is this:

This works fine.. UNLESS the number of characters in that cell [meaning Len(Range("StandardOtherTerms"))] gets
large, I am guessing >255. After this happens, the above does not work anymore. Any ideas why or a better way to do this?

I have a data base of names and addresses with the address in one field with
the seperator being a carriage return. I would like to chane that so I can
use the text to columns function in Excel

I might be stupid but I can't figure out how to return TODAY + a
specified time in a Cell

What I want to achieve is TODAYS date (this changes daily) and a
specified Time. So for example I wish for today to show 06/01/07
10:30am; tomorrow will show 07/01/07 10:30am


Hi I want to put hard returns into a cell but everytime I hit the enter
button it goes to the next cell. Does anyone know how to add hard returns to
a cell. Thanks in advance.


I wonder if anyone can help me

I am creating a sheet where I have a drop down box with a list of different products in and I want a value in a cell below to be entered depending on what is selected in the drop down box

My drop down box is in


It has a list of 8 items in (list is contained in the next sheet)

I want a letter to be entered in the box below depending on what product is selected. Each product has a unique letter

Could somebody help me with this please, and then I can apply it to the remaining items in my product coding sheet

Thank you all very much


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:

    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.


Hi There,

Is there a way to put a hard return in a cell so that the text will be on two separate lines within the cell?

So it would like like the text was simply wrapped within a cell, but the break would be at a predetermined place.

Thanks for your insight!

Excel does not seem to have a feature for entering hard returns in a cell.
An example is when you are entering an address. How do you get that
information into one cell?

I have four ranges named A, B, C, D.
I need to be able to copy (and paste) one of them when its name is
entered in a cell.
What is the macro syntax to get the value in a cell and use it to select
a range, please?
I would attach this macro to a button (which I know how to do) to allow
the user to enter the range name and click the button to copy it to a
fixed location (that I know how to specify)

is it possible to show users their macro security setting in a cell? My
workbook has macro in it, so if they don't enable macros or have their
security set to high, the macros won't work. What I'd like to do it show the
user in a cell what his/her security setting is and based on the cell value
(if the macros are disabled), show a warning.

Can this be done?

If anyone can help I need to create a basic program to create an emergency collection note in case of system breakdown. basically I need to know if there is a way of triggering the copying of a row to another sheet by filling in a cell.
what I would like a macro to do in practice is to copy a row into a separate sheet when I enter an issue date in the row (see attached spreasheet, put a date in the input box and then copies to sheet 2). It needs to copy only the row when the date is entered and not the whole sheet as there could/will be more than one collection note.Obviously it needs to move down 1 row each time a new row goes into sheet 2 starting at row 10.

I will need to do something to clear sheet 2 at the end of each collection note but I can set up a button to do this.
Any help on how to achieve this would be appreciated

Hi, I have upgraded from office 2000 to 2003. Since the upgrade I can no
longer import my textfile into Excel or Outlook, because any carriage return
in a text field (text is marked with "", fields are separated with will be
recognized as end of line / paragraph mark, consequently all following fields
are in the wrong order.

Does anyone know the trick to train the import function not to look for
carriage return marks in text fields?

Thank you!


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?


I have a worksheet that contains carriage returns embedded in a cell. When I
open the sheet, I do not see these carriage returns, but my co-worker opens
the same sheet, he sees these carriage returns as control characters. There
is an option in Excel 2003 that you can set to show/hide these control
characters using the following method
Tools -> Options -> Internations tab -> Show/Hide control characters.

I checked the whole Excel 2007 to accomplish the same task and couldn't find
anything of this sort.

Can you please let me know how I can accomplish this issue. Hope this is not
a bug in Excel 2007.

How do I write a formula to return all the text up to a carriage return character? I'm trying:


because I don't know how to express a carriage return in a formula. I've also tried char(10) and char(13), but it doesn't like any of those.

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