H HeatherJ Nov 25, 2009 #1 Is there a way to remove all spaces within a column of cells? Extra spaces are screwing up my subtotals. Thanks!
Is there a way to remove all spaces within a column of cells? Extra spaces are screwing up my subtotals. Thanks!
D Dave Peterson Nov 25, 2009 #3 Select the column Edit|replace what: (space character) with: (leave blank--not even a space character) replace all But.... Did you get this data from a web site? If yes, then you could be seeing those non-breaking HTML spaces. You can clean them up with formulas, but if you have to do it lots of times (or with lots of data), you may want to use a macro. David McRitchie has a macro that can help clean this: http://www.mvps.org/dmcritchie/excel/join.htm#trimall (look for "Sub Trimall()") If you're new to macros: Debra Dalgleish has some notes how to implement macros here: http://www.contextures.com/xlvba01.html David McRitchie has an intro to macros: http://www.mvps.org/dmcritchie/excel/getstarted.htm Ron de Bruin's intro to macros: http://www.rondebruin.nl/code.htm (General, Regular and Standard modules all describe the same thing.)
Select the column Edit|replace what: (space character) with: (leave blank--not even a space character) replace all But.... Did you get this data from a web site? If yes, then you could be seeing those non-breaking HTML spaces. You can clean them up with formulas, but if you have to do it lots of times (or with lots of data), you may want to use a macro. David McRitchie has a macro that can help clean this: http://www.mvps.org/dmcritchie/excel/join.htm#trimall (look for "Sub Trimall()") If you're new to macros: Debra Dalgleish has some notes how to implement macros here: http://www.contextures.com/xlvba01.html David McRitchie has an intro to macros: http://www.mvps.org/dmcritchie/excel/getstarted.htm Ron de Bruin's intro to macros: http://www.rondebruin.nl/code.htm (General, Regular and Standard modules all describe the same thing.)
D David Biddulph Nov 25, 2009 #4 Select your column Edit/ Replace: Find what: [type a space] Replace with: [leave empty] Replace All
E Excel Nut Nov 25, 2009 #5 Do you want to delete blank rows? Select the cells, press F5, Special, Blanks, OK. Then Delete etnire rows. Francis Hayes www.TheExcelAddict.com
Do you want to delete blank rows? Select the cells, press F5, Special, Blanks, OK. Then Delete etnire rows. Francis Hayes www.TheExcelAddict.com
H HeatherJ Nov 30, 2009 #6 wonderful! thanks so much for responding. Heather David Biddulph said: Select your column Edit/ Replace: Find what: [type a space] Replace with: [leave empty] Replace All -- David Biddulph HeatherJ said: Is there a way to remove all spaces within a column of cells? Extra spaces are screwing up my subtotals. Thanks! Click to expand... . Click to expand...
wonderful! thanks so much for responding. Heather David Biddulph said: Select your column Edit/ Replace: Find what: [type a space] Replace with: [leave empty] Replace All -- David Biddulph HeatherJ said: Is there a way to remove all spaces within a column of cells? Extra spaces are screwing up my subtotals. Thanks! Click to expand... . Click to expand...
