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.
5
  • 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. Sep 27, 2011 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, 2011 at 21:37
  • 1
    @sdoca you could post your own answer since it's the the best so far
    – barlop
    Sep 27, 2011 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, 2011 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, 2014 at 18:18

4 Answers 4

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.

1
  • The most simple and effective solution
    – Jules_Text
    Feb 4, 2017 at 5:44
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

2
  • Holy cow this worked great! This is such an annoying issue, thanks for helping out.
    – Webs
    Oct 20, 2014 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, 2017 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.

-2

In Alignment, click the Wrap Text button.

Demonstration

1
  • 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, 2011 at 19:47

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .