| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Deleting unwanted lead spaces throughout a worksheet
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Deleting unwanted lead spaces throughout a worksheet
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. Coles2020 wrote: 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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Deleting unwanted lead spaces throughout a worksheet
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. -- Hth Kassie Kasselman Change xxx to hotmail "Coles2020" wrote: 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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Deleting unwanted lead spaces throughout a worksheet
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.)? "JW" wrote: 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. Coles2020 wrote: 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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Deleting unwanted lead spaces throughout a worksheet
Kassie
That didn't work at all. It just deleted all the cells I highlighted. "kassie" wrote: 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. -- Hth Kassie Kasselman Change xxx to hotmail "Coles2020" wrote: 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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Deleting unwanted lead spaces throughout a worksheet
=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. -- David Biddulph "Coles2020" <Onward2000 wrote in message ... 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.)? "JW" wrote: 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. Coles2020 wrote: 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. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Deleting unwanted lead spaces throughout a worksheet
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"? "Coles2020" wrote: Kassie That didn't work at all. It just deleted all the cells I highlighted. "kassie" wrote: 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. -- Hth Kassie Kasselman Change xxx to hotmail "Coles2020" wrote: 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. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Deleting unwanted lead spaces throughout a worksheet
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 "Coles2020" <Onward2000 wrote in message ... Kassie That didn't work at all. It just deleted all the cells I highlighted. "kassie" wrote: 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. "Coles2020" wrote: 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. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Deleting unwanted lead spaces throughout a worksheet
What did you choose as delimiters? Spaces? Tabs?
-- David Biddulph "Coles2020" <Onward2000 wrote in message ... 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"? "Coles2020" wrote: Kassie That didn't work at all. It just deleted all the cells I highlighted. "kassie" wrote: 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. -- Hth Kassie Kasselman Change xxx to hotmail "Coles2020" wrote: 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. |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Deleting unwanted lead spaces throughout a worksheet
David
Thank you. The result was that those spaces are "non-breaking". Any idea on how to get rid of those? "David Biddulph" wrote: =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. -- David Biddulph "Coles2020" <Onward2000 wrote in message ... 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.)? "JW" wrote: 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. Coles2020 wrote: 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. |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Deleting unwanted lead spaces throughout a worksheet
David
I selected the colon ":" as the delimiter. "David Biddulph" wrote: What did you choose as delimiters? Spaces? Tabs? -- David Biddulph "Coles2020" <Onward2000 wrote in message ... 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"? "Coles2020" wrote: Kassie That didn't work at all. It just deleted all the cells I highlighted. "kassie" wrote: 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. -- Hth Kassie Kasselman Change xxx to hotmail "Coles2020" wrote: 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. |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Deleting unwanted lead spaces throughout a worksheet
You can do Edit/ Replace/ Alt0160/ [nothing]/ Close
Or =--SUBSTITUTE(A1,CHAR(160),"") -- David Biddulph "Coles2020" <Onward2000 wrote in message ... David Thank you. The result was that those spaces are "non-breaking". Any idea on how to get rid of those? "David Biddulph" wrote: =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. -- David Biddulph "Coles2020" <Onward2000 wrote in message ... 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.)? "JW" wrote: 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. Coles2020 wrote: 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. |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Deleting unwanted lead spaces throughout a worksheet
EUREKA!
David, thanks a bunch. That worked! "David Biddulph" wrote: You can do Edit/ Replace/ Alt0160/ [nothing]/ Close Or =--SUBSTITUTE(A1,CHAR(160),"") -- David Biddulph "Coles2020" <Onward2000 wrote in message ... David Thank you. The result was that those spaces are "non-breaking". Any idea on how to get rid of those? "David Biddulph" wrote: =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. -- David Biddulph "Coles2020" <Onward2000 wrote in message ... 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.)? "JW" wrote: 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. Coles2020 wrote: 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Unwanted spaces | Excel Worksheet Functions | |||
| Deleting unwanted data | Excel Discussion (Misc queries) | |||
| Deleting unwanted colums on a worksheet | Excel Worksheet Functions | |||
| Deleting Lead Zeros | Excel Worksheet Functions | |||
| Deleting unwanted Cells | Excel Discussion (Misc queries) | |||
Linear Mode
