Formula to insert alt enter into a cell

CATraveler

New Member
Joined
Jan 18, 2008
Messages
2
How can a formula perform the equivalent of the keyboard alt enter in a cell?
ie Make =A1&A2 where A1 and A2 are cells containing text become
a1text
a2text
in the new cell.
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Welcome to the Board!

You can use CHAR(10):

=A1&CHAR(10)&A2

Just make sure that Word Wrap is on and the row is set tall enough to see the wrap.

Hope that helps,

Smitty
 
Upvote 0
I'm interested in something similar, I want to have a cell where I store strings of information that are separated by CHAR(10). Is there any simple way to then extract the strings and put them into several cells?
Kind of doing the reverse of what CATraveler asked for.

Also I find it annoying when Excel automatically makes the cell taller when using the CHAR(10). How do I prevent this? (Basically the cell I want is purely for information storage, and to extract this info on demand in another worksheet).
 
Upvote 0
Antonio

If the resizing of the cell is a problem why not use another delimiter, like a comma.
 
Upvote 0
Welcome to the Board!

You can use CHAR(10):

=A1&CHAR(10)&A2

Just make sure that Word Wrap is on and the row is set tall enough to see the wrap.

Hope that helps,

Smitty

I think there was a typo - I believe it should be CHR(10) and not CHAR(10).
 
Upvote 0
Nope, Chr is a VBA function - CHAR is the worksheet function equivalent.:)
 
Upvote 0
Dear Sir,

I have similiar questions on how to make use of Macro to insert alt-enter into a cell with comma count per line, saying 5

is

C1,C2,C3,C4,C5,C6,C7
C8,C9,C10,C11

want to change the cell(s) as

C1,C2,C3,C4,C5,
C6,C7,C8,C9,C10,
C11

thanks,
Kevin
 
Upvote 0
Upvote 0
I'm interested in something similar, I want to have a cell where I store strings of information that are separated by CHAR(10). Is there any simple way to then extract the strings and put them into several cells?
Kind of doing the reverse of what CATraveler asked for.

Also I find it annoying when Excel automatically makes the cell taller when using the CHAR(10). How do I prevent this? (Basically the cell I want is purely for information storage, and to extract this info on demand in another worksheet).
Try this:

1. Select the column by clicking its heading label.

2. Data|Text to Columns...|Delimited|Next|Click in the "Other:" box|Hold the ALT key down and on the number keypad type 0010|Next|If you want to keep the original data then change the cell reference in the "Destination:" box to a vacant cell to the right|Finish

For your second question, try selecting the column then
Format|Cells...|Alignment tab|remove the check from "Wrap text"|OK
 
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,297
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top