Deleting unwanted lead spaces throughout a worksheet

[Guest]

I have FrontPage email me the result of a survey form -- with both the
question and the response. If the person selects a particular question the
result is "1". However, when I copy and paste the emailed results into Excel
the "1" is preceded by several spaces so that I cannot perform any
calculations.

Is there an easy way to remove those leading spaces from all cells (both
rows and columns) in my worksheets?

Thank you.

 

[JW]

Highlight the column, rows, or all cells and select Edit-->Replace.
In the find what field, enter a space. In the replace with field,
enter nothing. If more options are not available, click the Options
button. Make sure Match entire cell contents in not checked. Click
Replace All. This will get rid of ALL spaces in the selected cells.

 

[Guest]

copy an empty cell on your worksheet. Now select the range with the leading
spaces, right click, slect Paste Special, select the Add operation option,
and OK out. That normally converts numbers written as text back to numbers.

 

[Guest]

JW
I did this but was told there was nothing to replace. Does that mean that
the spaces before my "1"s are something else (i.e., tabs, etc.)?

 

[Guest]

Kassie
That didn't work at all. It just deleted all the cells I highlighted.

 

[David Biddulph]

=code(LEFT(A1)) will give you the ASCII code for the first character of the
string.
32 for a space. 160 for a non-breaking space.

 

[Guest]

NOTE
Here's something I just discovered. When I copied the email results into
Excel I selected the "Delimited" choice rather than the "Fixed" width choice
in the "Text to Columns" function. That is what put the extra "spaces" in
front of my "1" entries. Does that help anyone to know how to get rid of
those "spaces"?

 

[David Biddulph]

It sounds as if there is something very strange with your setup. It
ouightn't to do that. It'll be interesting to hear what's in the cells.

 

[David Biddulph]

What did you choose as delimiters? Spaces? Tabs?

 

[Guest]

David
Thank you. The result was that those spaces are "non-breaking". Any idea on
how to get rid of those?

 

[Guest]

David
I selected the colon ":" as the delimiter.

What did you choose as delimiters? Spaces? Tabs?

 

[David Biddulph]

You can do Edit/ Replace/ Alt0160/ [nothing]/ Close
Or =--SUBSTITUTE(A1,CHAR(160),"")

 

[Guest]

EUREKA!

David, thanks a bunch. That worked!

You can do Edit/ Replace/ Alt0160/ [nothing]/ Close
Or =--SUBSTITUTE(A1,CHAR(160),"")
--
David Biddulph

David
Thank you. The result was that those spaces are "non-breaking". Any idea
on
how to get rid of those?