6

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.
karel
  • 13,488
sdoca
  • 690
  • 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
  • John below solved this issue for me. My issue was due to having ASCII characters that Excel didn't like. – Webs Oct 20 '14 at 18:18

4 Answers4

5

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.

Cathy
  • 51
1

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

John
  • 11
  • Holy cow this worked great! This is such an annoying issue, thanks for helping out. – Webs Oct 20 '14 at 18:17
  • This resolved so many problems I was having with my data. It is essential to follow the method above. The first 48 times I tried to rid myself of the offending "character" I pasted into Word, used Show/Hide hidden formatting marks, selected what looked like a suspicious "space" character, and then used Find/Replace in Excel - or something equally as roundabout. None of that worked. When I used John's method I was told that more than 900 replacements had been made and virtually all the problems with my data were resolved. – That Idiot Aug 15 '17 at 19:03
0

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.

Chuck
  • 1
-2

In Alignment, click the Wrap Text button.

Demonstration

Christopher Rayl
  • 125
  • 2
    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