Deleting blank spaces (copied from Word doc) from Excel cells

[alex17]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

I just imported an outline fr Word into Excel (2008 Mac). There are 4 leading spaces in front of many of the text entries, I'm trying to delete them. In case it matters, these are all text entries, no data.

Doing a Replace and entering 4 spaces doesn't work. Doing the same thing w/ the 4 spaces enclosed in quotes (i.e. " ") also doesn't work. Using the =TRIM() function as noted in another post here does not work. The only thing that does work, so far, is manually editing each cell, navigating to the left of the text, and manually deleting the 4 spaces. Ugh, there must be a better way.

Any suggestion?
Thanks very much.

 

[JE McGimpsey]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

I just imported an outline fr Word into Excel (2008 Mac). There are 4 leading
spaces in front of many of the text entries, I'm trying to delete them. In
case it matters, these are all text entries, no data.

Doing a Replace and entering 4 spaces doesn't work. Doing the same thing w/
the 4 spaces enclosed in quotes (i.e. " ") also doesn't work. Using the
=TRIM() function as noted in another post here does not work. The only thing
that does work, so far, is manually editing each cell, navigating to the left
of the text, and manually deleting the 4 spaces. Ugh, there must be a better
way.

Any suggestion?

If TRIM() doesn't work, then the "spaces" aren't actually spaces. If the
text had been pasted from a web browser, I'd guess they were
non-breaking spaces, which can be removed by

=SUBSTITUTE(A1,CHAR(160),"")

However, since they were "imported...fr Word" I'm not sure - how did you
"import"? Copy/Paste?


You can remove some other characters (such as tabs) with CLEAN().

 

[Carl Witthoft]

If TRIM() doesn't work, then the "spaces" aren't actually spaces. If the
text had been pasted from a web browser, I'd guess they were
non-breaking spaces, which can be removed by

=SUBSTITUTE(A1,CHAR(160),"")

However, since they were "imported...fr Word" I'm not sure - how did you
"import"? Copy/Paste?


You can remove some other characters (such as tabs) with CLEAN().

In many cases like this, I've had success by going into a
representative cell, selecting and copying the blank region, and pasting
whatever it is into the FIND box.

 

[Alex]

Thanks for all the suggestions. Finally, the last suggestion worked, copying the blank region and pasting into the find box.

For what it's worth, this text was copied from a Word outline that had a couple levels of nested bullets, I was trying to get each nested level into a separate column and delete the bullet characters and spaces. I deleted the bullets, couldn't delete the space in between. It didn't seem to either spaces or tabs, but using the backspace key manually did get rid of the blank spaces, so I'm not sure what the actual characters were. (Gee, where's Reveal Codes when when you need it?)

So, done, thanks for the help.