Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to remove a space
Dear NG:
I copied and pasted my E-Z Pass statement into Excel as HTML. I sorted the data by tag number. If I highlight the cells with the "toll charges" for a specific tag number I SHOULD get the total toll charges in the lower status bar. Instead I get nothing. Apparently there is a blank space in front of each entry in the toll charges column. If I manually remove the blank space the charges total up in the status bar as they should. Is there any way to remove the space in front of each entry in the toll column other than manually removing them? I have a feeling I'm missing something very simple here. Thanks in advance for any suggestions. -Kevin |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to remove a space
One way is to use a helper column with the formula:-
=trim(a1) Drag this down for the length of your data and leading/trailing spaces will be removed. Mike "Kevin" wrote: Dear NG: I copied and pasted my E-Z Pass statement into Excel as HTML. I sorted the data by tag number. If I highlight the cells with the "toll charges" for a specific tag number I SHOULD get the total toll charges in the lower status bar. Instead I get nothing. Apparently there is a blank space in front of each entry in the toll charges column. If I manually remove the blank space the charges total up in the status bar as they should. Is there any way to remove the space in front of each entry in the toll column other than manually removing them? I have a feeling I'm missing something very simple here. Thanks in advance for any suggestions. -Kevin |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to remove a space
Copying from web pages can be a problem. Sometimes what looks like a space
character isn't really a space character--it's one of those HTML non-breaking spaces. David McRitchie has a macro that cleans up this kind of stuff: http://www.mvps.org/dmcritchie/excel/join.htm#trimall (look for "Sub Trimall()") If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Kevin wrote: Dear NG: I copied and pasted my E-Z Pass statement into Excel as HTML. I sorted the data by tag number. If I highlight the cells with the "toll charges" for a specific tag number I SHOULD get the total toll charges in the lower status bar. Instead I get nothing. Apparently there is a blank space in front of each entry in the toll charges column. If I manually remove the blank space the charges total up in the status bar as they should. Is there any way to remove the space in front of each entry in the toll column other than manually removing them? I have a feeling I'm missing something very simple here. Thanks in advance for any suggestions. -Kevin -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to remove a space
Dave Peterson,
Copying from web pages can be a problem. Indeed. The Trimall() macro did the trick. Thanks. -Kevin "Dave Peterson" wrote in message ... Copying from web pages can be a problem. Sometimes what looks like a space character isn't really a space character--it's one of those HTML non-breaking spaces. David McRitchie has a macro that cleans up this kind of stuff: http://www.mvps.org/dmcritchie/excel/join.htm#trimall (look for "Sub Trimall()") If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Kevin wrote: Dear NG: I copied and pasted my E-Z Pass statement into Excel as HTML. I sorted the data by tag number. If I highlight the cells with the "toll charges" for a specific tag number I SHOULD get the total toll charges in the lower status bar. Instead I get nothing. Apparently there is a blank space in front of each entry in the toll charges column. If I manually remove the blank space the charges total up in the status bar as they should. Is there any way to remove the space in front of each entry in the toll column other than manually removing them? I have a feeling I'm missing something very simple here. Thanks in advance for any suggestions. -Kevin -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to remove a space
Kevin
the fastest way to do this is highlight the column that has the toll charges, go to Edit- Replace and just replace the space with nothing (i.e. put a space in the find box and leave the replace box empty) Hope this helps. Tim On Jul 17, 8:46 am, "Kevin" wrote: Dear NG: I copied and pasted my E-Z Pass statement into Excel as HTML. I sorted the data by tag number. If I highlight the cells with the "toll charges" for a specific tag number I SHOULD get the total toll charges in the lower status bar. Instead I get nothing. Apparently there is a blank space in front of each entry in the toll charges column. If I manually remove the blank space the charges total up in the status bar as they should. Is there any way to remove the space in front of each entry in the toll column other than manually removing them? I have a feeling I'm missing something very simple here. Thanks in advance for any suggestions. -Kevin |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to remove a space
Tim,
I tried that first thing. That didn't work. Dave Peterson explained why in his post about it not actually being a space but rather a HTML non-breaking space. Aside from using the macro in DP's post I was also able to use Find and Replace by holding down Alt and entering 0160 in the Find feild and leaving Replace blank. -Kevin "Tim879" wrote in message oups.com... Kevin the fastest way to do this is highlight the column that has the toll charges, go to Edit- Replace and just replace the space with nothing (i.e. put a space in the find box and leave the replace box empty) Hope this helps. Tim On Jul 17, 8:46 am, "Kevin" wrote: Dear NG: I copied and pasted my E-Z Pass statement into Excel as HTML. I sorted the data by tag number. If I highlight the cells with the "toll charges" for a specific tag number I SHOULD get the total toll charges in the lower status bar. Instead I get nothing. Apparently there is a blank space in front of each entry in the toll charges column. If I manually remove the blank space the charges total up in the status bar as they should. Is there any way to remove the space in front of each entry in the toll column other than manually removing them? I have a feeling I'm missing something very simple here. Thanks in advance for any suggestions. -Kevin |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I remove a space between a letters and set of numbers? | Excel Discussion (Misc queries) | |||
Remove Space in Text | Excel Discussion (Misc queries) | |||
How do I remove white space around a chart? | Charts and Charting in Excel | |||
How do I remove a hidden space in Excel cells | Excel Discussion (Misc queries) | |||
Remove leading space | Excel Worksheet Functions |