Simulating Alt+Enter in a Formula

@Roger
Also, for a comparison of VBA functions with Excel worksheet functions, see https://www.techonthenet.com/excel/formulas/index.php
and for a detailed VBA reference, see
https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/visual-basic-language-reference

@Roger
1. The correct VBA function is Chr(10), not CHAR(10). The latter is Excel's equivalent worksheet function.
2. The string concatenation operator & should be surrounded by space characters, but I believe the VB Editor (VBE) does this automatically for your expression.
3. Instead of Chr(10), you could use VBA's constant vbLf, but in this case you must manually add a space character after each vbLf. VBA interprets a numeric constant or variable immediately followed by & as declaration of a Long constant or variable; in this case, & is a Long type-declaration character.
4. Before running your macro, use VBE's Debug > Compile to locate any errors.

I have reviewed this tip as I am working on a yearly expense spreadsheet. I have used the &Char(1)& command in some of my other areas of the sheet but not in a macro for a new year. I have the following:
Range("$AF$6").Value = "Account Number: 12345678"&CHAR(10)&"KWH Used:______"&CHAR(10)& "https://p-auth.PowerCompany.com/my-account/sign-in".
When I hit enter the formula looks like this
Range("$AF$6").Value = "Account Number: 12345678" & CHAR(10) & "KWH Used:______" & CHAR(10) & "https://p-auth.PowerCompany.com/my-account/sign-in"

If I run it Excel replies back a compile error. has occurred. I have tried looking on line but nobody addresses it being in a macro using a .value function. Can anyone tell me what I am missing in the above examples.

I have a download from my HRIS that exports job titles into a single cell with wrapped text and two hard returns in between so they are separated but still all on one row. To get the titles into separate rows I've had to edit the one cell by deleting the extra returns, inserting a semi-colon between the titles and then using Text to Columns delimited to separate. Then I have to transpose the columns into rows. Sometimes the cell has over 150 titles so this method is quite tedious. Is there a more automated way to get the list of job titles into separate rows?

CHAR(10) is a line feed, not a carriage return-line feed. Many systems do not return the carriage when a CHAR(10) is encountered. Excel chooses, like many other systems to do so, but is is a choice, not a definition. CHAR(13) is the explicit carriage return.

Sorry for being not quite on topic but is there a method to get the Excel shortcuts back to their defaults.
In particular a Ctrl + Shift + End that is not highlighting the whole selection. It just goes to the last cell like its Shift doesn't get seen. Shift key works in normal operation.
Windows 10 so haven't had any success trying the terms referred in this tip.

The vbLf and vbCrLf constants can be used in macros, but not in formulas.

Thanks very much

In Excel 2010 (and maybe other versions) when saving the file as a PDF and an "unknown char" is also printed (a question mark inside a square box), this doesn't display in Excel only in the PDF version.

The only workaround I've found so far is to pad out, at the point I want the break to occur, with lots of spaces.

Can anyone suggest an alternative?

I think vbLf is a predefined constant for linefeed, equivalent to CHAR(10), and vbCrLf is CarriageReturn + LineFeed

Thank you very much for this tip, it's very nice!