I do a lot of text manipulation in Microsot Word, and I’m really familiar with how to use Word to massage text into the form I want. I can really make MS Word sing and dance.
But I’m not as good with MS Excel. I can only make Excel skip and hum.
So I’ve got a couple of questions for any Excel Experts out there:
1) I’m constantly having to re-set the default cell alignments for every spreadsheet I touch. Invariably, I want the text in cells aligned to the top, with shrink to fit and word wrap turned ON. What I want to do is LOCK THE DEFAULTS to my preferred settings so that I don’t have to re-set the alignment every single time. There’s gotta be a way to do this, even if it means monkeying with a dll. Anybody know how?THIS PROBLEM SOLVED IN THE COMBOX. WOO-HOO!
2) I also need to find a way to insert carriage returns/paragraph marks/blank lines within text cells. Yet, as you know, hitting Enter does not produce the desired effect. Further, pasting multiple paragraphs from another app (like Word) results in the text being put into different cells. How can this be overcome? THIS PROBLEM PARTLY SOLVED IN THE COMBOX (THANKS!), SO HERE’S A CLARIFICATION: I also need to be able to paste text into Excel from Word that will go into several cells some of which contain line breaks within the cell (e.g., Cell 1, Cell 2 which has a line break in it, Cell 3 which doesn’t, etc.). I imagine that I need one kind of code to use as a cell break and another to use as a line break. Any ideas? Thanks!
¡Muchas gracias, mis amigos!
Jimmy,
You might want to consider accomplishing this through the visual basic feature.
Without Excel in front of me, I believe the procedure you want is to select all cells, make the cell property changes you want, and the select save template (rather than save), and save it over the default template.
In regards to point #2, when you do the paste, already have the cell in editing mode (this can usually be accomplished by a double click) and then try pasting. What you may also be experiencing is the default overflow behavior when there is no data in the adjoining cell. If you have a large text area to fill, select all the cells where you want it to appear, and choose merge. This might help isolate your problem.
To insert a line break press ALT + ENTER while in edit mode. To enter edit mode if not already there, double-click the cell or press F2.
With regard to saving a new default template, I found this in Excel 2003’s Help:
If you’ve created a template named Book.xlt or Sheet.xlt and saved it in the XLStart folder, Microsoft Excel uses the template to create new default workbooks (default startup workbook: The new, unsaved workbook that’s displayed when you start Excel. The default startup workbook is displayed only if you haven’t included other workbooks in the XLStart folder.) or to insert new worksheets. The XLStart folder is usually located at:
C:\Program Files\Microsoft Office\Office11\XLStart
Can someone please explain to me WHAT THE HECK JIMMY IS TALKIN’BOUT?
Thanks, guys! Problem #1 is solved!
Ed: It’s all very electrical and scientific.
Animadversor is right about problem 2. Just put the cursor where you want the carriage return within the text field and hit alt + enter.
Further, pasting multiple paragraphs from another app (like Word) results in the text being put into different cells.
This in #2 sounds like a delimit issue.
The only method by which I’ve handled this in the past is through scripts.
Jimmy,
Before you paste Word text into a big cell put a apostrophe or tick mark (‘) in the cell first, then paste as usual. Your paragraphs will be maintained in the cell.
I hope that wraps up all your questions. 🙂
I think you need a hammer.
It’ll make you feel better, at least.
Jimmy,
Maybe it’s just me, but I can’t figure out what you mean in the red-text question under your part 2.
I’m thinking of something like this: TEXT TEXT | TEXT
| |
TEXT
Well, well, well, this discussion sounds like my sword class…
1.Place feet together and point sword.
2.Thrust backwards independently.
3.Rotate upper body to the left and make independent step with toes vertical.
4.When body goes left the sword fingers goes the wrong direction along with the left rib.
5.Split independent
6.Sword withdraws and feet retire
7.Bring diagonally and shrink with left bow step.
I’ll spare you the remainder of the 32 step double-edge sword form (this is the SHORT form- try 53 for the LONG form) and instead I’ll light a few hundred candles in front of my laptop!
NB: I can actually do all of the 32 short form steps, but it’s not pretty.
Jimmy, I’m pretty sure I could sort this out for you with a macro. But to make it easy it would be great if you could email me an example of the Word doc you’d start with and the Excel spreadsheet you’d want it to be turned into. As it stands it’s not entirely clear what you have in mind. But it sounds definitely do-able.
I would advice against putting multiple paragraphs in a single cell. With Excel you can enter up to 32,767 characters in a cell but only 1024 will be visible or
printed unless you click the cell and actually view the contents. As stated above a macro may work, but again you are really going beyond the capabilities of Excel and you will probably not be satisfied with the results.
Just tested JohnD’s instruction above (to put an asterisk in the cell and then paste the Word text) and it works like a charm. This has been bugging me, too. I knew about Alt+Enter, but not how to maintain paragraphs when copying from Word into Excel. This is a huge help for me at work. Thanks, JohnD!
Get a Mac. LOL!
A quick aside. Esau, I love programming as much as the next guy. Unfortunately being able to program often leads you to use programs how they are not intended and should be avoided.
As best I can tell, you have limited utility for excel. Whatever table it is that you are creating, which I’m assuming is the purpose of your use of Excel, just go ahead and make it. Copy and Paste that table into your Word document. (You could also link and embed if you like.) Word also has native table support of its own that may be sufficient for your needs. Regardless, Word is the software you want to be using for text formating.
If you really want to go extreme, go Insert->Object and select Microsoft Word Doc. You can adjust the boundaries of the object anyway you like, and they will appear over the cells.
Jimmy,
Along the lines of Greg’s and MZ’s comments, maybe you could tell us what you’re doing and why. It’ll probably improve the quality of the tips you get.
A) Don’t trust excel VB macros. They’re evil and don’t work the same way twice (in my experience.)“. Including the double quotes, and only a line feed, not a carriage return (carriage return shows up as a block if you put it in there.) So this:
blah blah”
goodbye goodbye”-a to highlight everything, -c or -insert to copy, then goto Excel, and -v or -insert to paste.
B) Guys, this sounds like a secret project! Now’s our chance! Don’t tell him anymore until he fesses up and tells us what he’s doing!!!!
C) OK, someone else is going to have to help if they know of an editor that can ease this process. It seems that the content of a cell that has multiple lines in it is “
“blah blah
“hello hello
will put in cell 1
blah blah
blah blah
and in cell 2
hello hello
goodby goodbye
So here’s a suggested solution, put double quotes around each cell (this only works for rows, haven’t played around for multiple columns yet), then copy and paste into notepad so its unformatted text. If you don’t do the notepad step, then word will try to inter operate with Excel and it’ll end up tying your hands.
Save the file, grab the program ‘dos2unix.exe’ from somewhere off the web (cygwin or minsys for example), run dos2unix(I believe minsys has dos2unix) on the file, then you can open the file in notepad again,
If you’re quoting someone in your text, you can’t use the double quote “, so use single quotes ‘
If Andy can make a snarky comment about a Mac, then I will make a pitch for the best word processor ever: WordPerfect.
The “reveal codes” (Alt-F3) is worth the price of admission.
I’m not sure what Jimmy wants to do with lots of text in Excel, but I agree with M.Z. that the tables feature in Word might be the ticket.
OK, that got messed up because I used < and > in my typing. 🙂 You’d think I’d remember that.
C) OK, someone else is going to have to help if they know of an editor. It seems that the content of a cell that has multiple lines in it is ” < text > < linefeed > < text > “. Including the double quotes, and only a line feed, not a carriage return. So this:
“blah blah<linefeed>
blah blah”
“hello hello<linefeed>
goodbye goodbye”
will put in cell 1
blah blah
blah blah
and in cell 2
hello hello
goodby goodbye
oh yeah, and:
then you can open the file in notepad again, <ctrl>-a to highlight everything, <ctrl>-c or <ctrl>-insert to copy, then goto Excel, and <ctrl>-v or <shift>-insert to paste.
I’m done.
Regarding your update of the 2nd Excel concern, I believe if you make a table in Word, you can format it all you want (e.g., carriage returns, indents, bullet points, outline numbers/letters, etc.). Then highlight the table in Word, copy it (CTRL-C or the “Edit -> Copy” menu), switch to Excel, select the cell where you want it to start, and paste it (CTRL-V or “Edit -> Paste” from the menu).
That *should* work, although I believe any “multi-line” cells will be multiple rows in Excel.
I’m not done…as I though I was…
In word, [tools]->[autocorrect options]
goto the [autoformat as you type] tab and turn off the “straight quotes” to “smart quotes” tab.
Then save the document as a text file. A dialog should pop up that allows you to change an entry that says “End lines with:” and should have ‘CR / LF’ in the box. Change the ‘CR / LF’ to just ‘LF’.
Now you can open the file in notepad and copy and paste into Excel (you can’t seem to paste directly from Word afaik.)
If Andy can make a snarky comment about a Mac, then I will make a pitch for the best word processor ever: WordPerfect.
vi is the best word processor ever.
A quick aside. Esau, I love programming as much as the next guy. Unfortunately being able to program often leads you to use programs how they are not intended and should be avoided.
Thanks M.Z. Forrest for the charitable advice. =^)
Believe me, I went through a class of VBA and would normally refer to my VBA for Excel bible prior to accomplishing anything in VBA code.
It helps a lot though for me, personally, since you can do things (e.g., customize project events in order to make Excel run like your own customized program), as had been taught us by our teacher then, that you couldn’t do normally within Excel’s typical Macros; which, I acknowledge, may coincide with your point here.
I advised Jimmy accordingly as it appears he has some programming background judging from the nifty things he does (and has mentioned) on his blog.
However, so long as you’re not violating the normal parameters of the Excel program itself and adhere to acceptable VBA programming techniques, I wouldn’t think that there would be any actual harm done by performing such programming that was originally intended by the creators of Excel for the very convenience of the Excel VBA user/programmer or else they wouldn’t have allowed such in the first place.
Although, I would greatly appreciate any expert tips you might have concerning it since you seem to have a lot of knowledge about programming. ;^)
Esau,
I’m not a huge fan of macros. Generally, macros and programming are the equivalent of taking a sledgehammer to a fly. With what you are doing, general convenience features applicable to all your work, I don’t have a problem. The problem generally comes up when upgrades happen. Particularly on the business side, you have to change all your macros and re-debug your programs. Additionally, you build a program for one problem, and then you get another problem that you want to use your program for so you are always tweaking your program. If a person would have learned to do the task Excel’s way the first time, in the long run he would have been more efficient.
My personal recommendation is that you keep any programming in VBA to massaging input/output processes. For example, the database import utility in Excel is sufficient for most things, but every now and then you get a dataset that doesn’t import well. For instance, your data might have the date in yyyymmdd format – this issue might be resolved. Sometimes brute force is needed. The other time is when you want to automate a process. Automation is a great thing, but most things I’ve seen people automate are one and done deals. For example, I’ve known people to write VBA scripts to remove a character at the end of a cell for a 25 item list. While it makes a great textbook example, it is silly for someone to spend their time programming a 1-2 minute task of hitting the old delete key.
M.Z. Forrest:
You’re awesome!
Thanks for the expert advice!
I typically resort to VBA code since there are things I can’t do with Excel’s normal menu features that I can do through VBA program code (which our teacher had taught us in that class I mentioned).
There are certain features in Excel that can only be invoked through specific VBA code.
Also, it really helps to make one efficient in their activities in that you can tailor the Excel program to open a workbook with certain personal features (such as a customized menu and toolbar of my own making) as well as programmed menu-driven capabilities; add to that, you venture into a number of other possibilities and can attempt interface with other MS Office programs, like Access.
M.Z., I thank you though and please feel free though to provide any such advice! This is certainly welcomed and appreciated!
Jimmy,
For #2, to paste a paragraph into a specific cell, first select the cell where you want the paragraph placed, then click the mouse button in the field entry area (below the tool bars, where the little “fx” is). Paste your paragraph up there and it should retain the line and paragraph breaks. (I just had to complete a similar exercise last week, worked well).
Jimmy,
I just re-read your clarification and some of the other comments; looks like I missed a subtlety. Probably not quite the new information you were look for, sorry.
I’m thinking of something like this:
TEXT TEXT TEXT TEXT
I apologize, folks, but when I wrote the above I didn’t realize that part of my code wasn’t displaying. The above makes no sense at all! Mea culpa!
Here’s what I meant to say:
What I’m thinking of is something like this:
TEXT –cell break– TEXT –line break– TEXT –cell break– TEXT
I’m wanting to format something in Word that has cell breaks in it and line breaks within some cells. Then I want to paste it into Excel and have both the material (with the embedded line breaks) show up within the cells I set up in Word.
Thus the above string would have three cells, the middle of which would have a line break within the cell.
At present, it doesn’t do this. Excel treats every cell break *or* carriage return as a cell break, and so the above TEXT string would end up in four cells, not three as I want it.
Hope that clarifies things!
Well..looking at Allen’s comment, which is very interesting to know…
Play around with highlighting text in word while holding down the Alt key. You’ll figure out if its useful or not for your purposes soon enough.
At present, it doesn’t do this. Excel treats every cell break *or* carriage return as a cell break, and so the above TEXT string would end up in four cells, not three as I want it.
Jimmy,
It appears that Excel is delimitting this according to the cell break/carriage return (at least, that’s how I’m interpreting it).
Again, I would advise importing it via VBA code; also, that way, if ever you need to do this ever again, you can take advantage of the code the next time around.
I’d provide programming input on it except that I really don’t quite know what you’re actually trying to accomplish here.
However, I think M.Z. Forrest might have better ideas on the matter.
M.Z.,
Any advice?
One other thing:
I might advise (that is, if you rather do this manually) that you consider instructing Excel to delimit with respect to a designated symbol (maybe perhaps even utilizing the typical comma-delimited method) so that it doesn’t mistake your cell-breaks and carriage returns as actual delimits and, therefore, you end up having your text strings appear in four cells, as you have indicated.
Given your description, I’m assuming you are trying to an inset table for a page, like a captioned photograph. To just accomplish what you stated, in Word I would:
Menu bar->Table->Draw Table… Create the table where you want it. You can adjust it later.
Right click in the new table, and select “Split Cells…” Select 3 columns.
Paste the text into the respective cells. I had no prombem placing a carriage return in the cells unlike Excel where I had to hold Alt and press Enter.
Jimmy:
I take that back, ignore my example above that said: maybe perhaps even utilizing the typical comma-delimited method
You might actually have commas in your text (?) and so it’s better to go with something unique to indicate as your delimit and choose a symbol not present in your text string.
My favorite delimeter is an ~.
ditto!!!! ;^)
My favorite delimeter is an ~.
Hey, wait a minute… never mind. =^)
MZ,
It sounds like Jimmy might already have 3 cells in a Word document table from left to right. The middle cell alone has two paragraphs.
I think he wants the same setup in Excel for some reason, and he wants to copy it in the least number of operations, instead of having to paste the contents of each cell separately. I assume he wants to do this at least hundreds of times, or that this encompasses a lot of data. Where the appeal of Excel comes in, he hasn’t told us yet.
Correct me if I’m wrong, Jimmy.
…he wants to copy it in the least number of operations, instead of having to paste the contents of each cell separately. I assume he wants to do this at least hundreds of times, or that this encompasses a lot of data. Where the appeal of Excel comes in, he hasn’t told us yet.
From this comment alone, it would appear that VBA would be the best way (although, again, MZ might carry better insight) since it would automate this operation versus having to do such things manually. It is instant and plus you could take advantage of it for future use.
Thanks JohnD. Doing that, I’m able to replicate Jimmy’s experience. I’m at a loss how to rectify it, outside VBA. If you create a dummy table in Word with properties Jimmy and JohnD describe and then a Table->Convert->Table to Text… you can see how the OLE is occuring and why it is outputting in Excel as it is.
Bingo!
Save the Word doc as an html file.
Open the HTML file in a web browser, and copy and paste.
Doh! Spoke to soon.
So, have any of you guys gotten any work done today?
So, have any of you guys gotten any work done today?
There is such a thing called Multi-Tasking.
But I think I’d pick a character that doesn’t appear anywhere in the data in
MSWord. (Like a vertical bar |).
then in Word
select your table
Edit|replace
And replace both the “paragraph mark” and the “manual page break”.
(You can find them under the “special” button in Word’s find dialog)
(They both cause problems in excel.)
Change them both to the unique character.
Paste into Excel.
Select the range of cells and do
Edit|replace
change the unique character (|) to alt-enter’s.
Hit and hold the Alt key and type 0010 into the “replace with” box.
(alt-0010: That 0010 is typed using the number keypad–not the keys above
QWERT.)
It may not look like it took, but you’ll see the results when you hit the
“replace all”
If you do this, you’ll get the same linebreaks that you had in Word (maybe widen
the columns).
From a google search:
http://groups.google.com/group/microsoft.public.excel.misc/browse_thread/thread/f689056b98a32319/1a97c20242dbbbc8%231a97c20242dbbbc8
But I think I’d pick a character that doesn’t appear anywhere in the data in
MSWord. (Like a vertical bar |).
Actually, MZ, that’s what I meant when I said:
That’s awesome, though — I wasn’t aware of the website you referenced!
Thanks!
By the way, did it work when you tried it?
This looks like a job for the folks At Rockwell
ALT+ENTER for a line break. Noting this is a two year old post (happy birthday today) – you probably figure this out already.
ALT+ENTER for a line break. Noting this is a two year old post (happy birthday today) – you probably figure this out already.