Forcing a line feed within a cell (Carriage Return)

**Marcus Gee** · 10-22-2007, 09:43 AM

good morning - I've got a spreadsheet that I'm interested in applying a find a replace function to. I'm not even sure Excel does this (let alone VBE), but I thought I'd ask anyway. The information below is an example of what I have throughout one column:

ZINC INFO:\n2-1 3/4'SL\n2-MTTB\n2-DD\n2-MRB\n\nProp Size: 26L/R26 (3 Blades)\nOTHER:

The program that I'm loading the data from inserts "\n" for a line feed, and I'd like to be able to find and replace the "\n" with a line feed (within the cell) when it finds "\n". Does anyone know of a way to do that?

Thanks in advance, and happy Monday!

Marcus

**dominicb** · 10-22-2007, 09:54 AM

Good afternoon Marcus Gee

I'm not even sure Excel does this

Sure does. Just press Alt + Enter.

let alone VBE

Does this too. Not sure which one you're after, but here goes :

Sub test()
Range("A1").Value = "happy" & vbLf & "birthday"
End Sub

HTH

DominicB

**VBA Noob** · 10-22-2007, 09:56 AM

A few ways

vbCr
vbCrLf
vbLf
vbNewLine
Chr(10)
Chr(13)

VBA Noob

**dominicb** · 10-22-2007, 10:04 AM

Afternoon Noob

Originally Posted by VBA Noob

vbCr
vbCrLf
vbLf
vbNewLine
Chr(10)
Chr(13)

Only vbLf and Chr(10) are suitable for use in a worksheet cell - all the rest add "invisible" line feed characters. Oddly enough, they don't appear when used to force a new line in a MsgBox.

DominicB

**Marcus Gee** · 10-24-2007, 01:12 AM

You guys are awesome. I've printed out the variety of ways and I'll give them each a try tomorrow. By the way, most of you helped with the scheduling macro in one way or another, so I wanted to let you know that we implemented over the past couple of days and they are blown away - works like a champ. Is there another forum or place that I can post macros to for others to use? There's really nothing propietary about the macro itself, and I thought it might be a good thing to share.

Thanks again - Marcus

**escrich** · 04-18-2013, 11:24 AM

Line feed and carriage return are codes 10 13, sorry, not eleven, but are still not working on an excel sheet, not using VBA, some examples:
=CONCATENATE(B4;CHAR(10);CHAR(13);I4) = same as if you don't write the char sentences
=CONCATENATE(B7;vbNewLine;I7) = cell shows #name?
=B8&CHAR(10)&CHAR(13)&I8 = same as if you don't include any thing related with char$

Resuming, nothing works

in other situations i was used something like that:

=IF(A116="";"";HYPERLINK("mailto:"&VLOOKUP(A116;Dirs!A:C;3;FALSE)&" ?subject=something%20to_write"&"&body="&VLOOKUP(VLOOKUP(A116;Dirs!A:H;8;FALSE);Textos!A:G;2;FALSE)&VLOOKUP(A116;Dirs!A:E;4;FALSE)&VLOOKUP(VLOOKUP(A116;Dirs!A:H;8;FALSE);Textos!A:G;3;FALSE)&D116&VLOOKUP(VLOOKUP(A116;Dirs!A:H;8;FALSE);Textos!A:G;4;FALSE)&"%0a%0d"&VLOOKUP(VLOOKUP(A116;Dirs!A:H;8;FALSE);Textos!A:G;5;FALSE)&"%0a%0d"&"Someone responsible";VLOOKUP(A116;Dirs!A:E;2;FALSE)))

note the use of &"%0a%0d"& but in this momment cell is creating HTML, in no other ocasions i have LF&CR working inside a cell.

Does anyone have something new?

**NBVC** · 10-22-2007, 09:57 AM

Do a Find/Replace

Find What: \n

Replace With: hold down Alt key and type 013 with the Numpad on the right of the keyboard. 011 is for carriage return