Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Is it possible to use the wrap text option in an Excel cell but not have it split words when autoresizing? For example, if I have this text in a cell "This is my column title", it would be okay when I autosized (double click the column border) to have:

This
is 
my
column
title

But not:

This is m
y column t
itle.
share|improve this question
    
It should work in all cases except when your column is narrower than a full word. So in your example, I get what you want unless the column is less than 53 pixels wide (using Consolas 11 pt), as "column" is 52 pixels wide, so it has to split the word. –  Rhys Gibson Sep 27 '11 at 19:58
1  
I've been doing some playing around and found that it does do what I want (makes the column wider if needed) AS LONG AS the row height forces it to. If the row height will allow for all the contents to be displayed, it won't resize the column width and produces split words. Make the row height smaller and autoresize the column and it works. Not sure if that makes sense; it's hard to describe in words what I mean. –  sdoca Sep 27 '11 at 21:37
1  
@sdoca you could post your own answer since it's the the best so far –  barlop Sep 27 '11 at 22:42
    
@RhysGibson But in his example the word "my" got broken..which isn't necessary since it's such a small word. So I suppose as sdoca said that row height is coming into play. –  barlop Sep 27 '11 at 22:45

4 Answers 4

In Alignment, click the Wrap Text button.

Demonstration

share|improve this answer
1  
Thanks, but the question is about disallowing the breaking of text in the middle of a word when text wrap is on and autosizing a column, not how to turn on text wrap. –  sdoca Sep 27 '11 at 19:47

Generally, newer versions of Excel will automatically break text properly between words.

However, when a different space character is used, it will not properly recognize the spaces between words, hence treat the entire text as a single word and break it at fixed widths. (I found this out the hard way, and took quite a bit of time to identify the issue.) When typing text in Excel, the spaces are normally ok (ASCII code 32). However, when copying and pasting text from some other applications, a different character may be used (ASCII code 160, for example, which is equivalent to a space character, except with even parity); Excel treats those differently.

You can use the formula =SUBSTITUTE(," "," ") to reproduce the cell replacing the "bad" space characters, where is the cell in question, the first " " contains the "bad" space character, and the second " " contains a normal space.

And maybe someone else out there has another suggestion on how to strip off the parity bit when copying text.

share|improve this answer

As an alternative to the substitute command, i did the following to get rid of problematic spacing characters that had been imported from MS Access and would not Word wrap.

In the problematic cell, Copy the last letter of one word, the space and the first letter of the next word

Do Ctrl F to pull up the find/replace drop down

Go to the Replace tab

Paste the 3 characters into the Find What box

Delete the two visible characters - leaving the problematic but invisible character

Go to the replace with box and hit the space bar once.

Then replace all

Worked for me anyway

share|improve this answer

I ran into the same problem when I copied text from a non-Excel source. For me it turned out to be a problem with how Excel was reading the spaces between the words, it did not recognize them as spaces.

I used find & replace to correct the spaced. I copied the "bad" space from the text into the find field and typed in a space into the replace field. To us meer humans both fields appear blank. This corrected my text wrapping issue.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.