|
Removing spaces in cells with data in it
We have a personnel roster that is kept in a database. Occasionally, this roster is exported into Excel and sent out to members of the department. In one column are dates that each person was employed. When I try to sort the entire roster, it doesn't come up right because most of the cells that contain the date have spaces in front of it. Sometimes it's 1 or 2, sometime more, other times there are no spaces. In order for me to sort the entire roster correctly, I'm having to manually remove the spaces in front of the date, then sort the roster according to date. There are over 500 entries in this roster, is there a better way to remove the spaces other than manually? Thanx... -- Ltat42a ------------------------------------------------------------------------ Ltat42a's Profile: http://www.excelforum.com/member.php...o&userid=24735 View this thread: http://www.excelforum.com/showthread...hreadid=391784 |
Ltat412a:
In a blank column, type this formula (this example assumes that the first date is in cell A2): =trim(A2) Then copy the formulat by double-clicking it down the 500 rows. You can then copy this column "over itself" by selecting the column, copy and then paste special values. You can then sort on that column. "Ltat42a" wrote: We have a personnel roster that is kept in a database. Occasionally, this roster is exported into Excel and sent out to members of the department. In one column are dates that each person was employed. When I try to sort the entire roster, it doesn't come up right because most of the cells that contain the date have spaces in front of it. Sometimes it's 1 or 2, sometime more, other times there are no spaces. In order for me to sort the entire roster correctly, I'm having to manually remove the spaces in front of the date, then sort the roster according to date. There are over 500 entries in this roster, is there a better way to remove the spaces other than manually? Thanx... -- Ltat42a ------------------------------------------------------------------------ Ltat42a's Profile: http://www.excelforum.com/member.php...o&userid=24735 View this thread: http://www.excelforum.com/showthread...hreadid=391784 |
Check out the TRIM function. This will remove all spaces from a cell (except single spaces between words) Use a 'helper' column next to your data (insert if needed), use this formula (copied down your range): =TRIM(A1) ---- of course, adjust the range as needed to match your data's location You can then Hide or Delete the original column. Does this work for you? Bruce tip: save your data before you attempt any altering process...just in case... -- swatsp0p ------------------------------------------------------------------------ swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101 View this thread: http://www.excelforum.com/showthread...hreadid=391784 |
What I have done in the past is an edit replace. Highlight the column you
wish to edit. From the main menu click on Edit - replace. When the box appears go to the replace tab. Next to "find what:" put one space. Next to "replace with:" leave it blank. Then choose "Replace All". This should remove all spaces. "Ltat42a" wrote: We have a personnel roster that is kept in a database. Occasionally, this roster is exported into Excel and sent out to members of the department. In one column are dates that each person was employed. When I try to sort the entire roster, it doesn't come up right because most of the cells that contain the date have spaces in front of it. Sometimes it's 1 or 2, sometime more, other times there are no spaces. In order for me to sort the entire roster correctly, I'm having to manually remove the spaces in front of the date, then sort the roster according to date. There are over 500 entries in this roster, is there a better way to remove the spaces other than manually? Thanx... -- Ltat42a ------------------------------------------------------------------------ Ltat42a's Profile: http://www.excelforum.com/member.php...o&userid=24735 View this thread: http://www.excelforum.com/showthread...hreadid=391784 |
carrie Wrote: What I have done in the past is an edit replace. Highlight the column you wish to edit. From the main menu click on Edit - replace. When the box appears go to the replace tab. Next to "find what:" put one space. Next to "replace with:" leave it blank. Then choose "Replace All". This should remove all spaces. "Ltat42a" wrote: We have a personnel roster that is kept in a database. Occasionally, this roster is exported into Excel and sent out to members of the department. In one column are dates that each person was employed. When I try to sort the entire roster, it doesn't come up right because most of the cells that contain the date have spaces in front of it. Sometimes it's 1 or 2, sometime more, other times there are no spaces. In order for me to sort the entire roster correctly, I'm having to manually remove the spaces in front of the date, then sort the roster according to date. There are over 500 entries in this roster, is there a better way to remove the spaces other than manually? Thanx... -- Ltat42a ------------------------------------------------------------------------ Ltat42a's Profile: http://www.excelforum.com/member.php...o&userid=24735 View this thread: http://www.excelforum.com/showthread...hreadid=391784 Thanks everybody for replying. I tried several times using the TRIM function. I was having trouble with this because it would often return the text equilvalent of the date, it would not show the date. I did try the replace idea, it seemed to work the best and removed all the spaces and left the dates in place - Thanx! -- Ltat42a ------------------------------------------------------------------------ Ltat42a's Profile: http://www.excelforum.com/member.php...o&userid=24735 View this thread: http://www.excelforum.com/showthread...hreadid=391784 |
You are using Text functions therefore the result will be text.
You can probably hit F2 then enter, if that fixes your date then there are several ways of solving the problem to simulate a reentry. Include selecting an empty cell, copying it Ctrl+C then selecting the column or other cells to be reentered and using Edit, paste special, add. But you would find it a lot easier to use a macro to remove the leading and trailing spaces, or what would appear to be spaces and essentially reenter their values. See the TRIMALL macro in http://www.mvps.org/dmcritchie/excel/join.htm#trimall If you need it there is also a link pointing you to Getting Started with Macros and User Defined Functions http://www.mvps.org/dmcritchie/excel/getstarted.htm --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Ltat42a" wrote in message ... carrie Wrote: What I have done in the past is an edit replace. Highlight the column you wish to edit. From the main menu click on Edit - replace. When the box appears go to the replace tab. Next to "find what:" put one space. Next to "replace with:" leave it blank. Then choose "Replace All". This should remove all spaces. "Ltat42a" wrote: We have a personnel roster that is kept in a database. Occasionally, this roster is exported into Excel and sent out to members of the department. In one column are dates that each person was employed. When I try to sort the entire roster, it doesn't come up right because most of the cells that contain the date have spaces in front of it. Sometimes it's 1 or 2, sometime more, other times there are no spaces. In order for me to sort the entire roster correctly, I'm having to manually remove the spaces in front of the date, then sort the roster according to date. There are over 500 entries in this roster, is there a better way to remove the spaces other than manually? Thanx... -- Ltat42a ------------------------------------------------------------------------ Ltat42a's Profile: http://www.excelforum.com/member.php...o&userid=24735 View this thread: http://www.excelforum.com/showthread...hreadid=391784 Thanks everybody for replying. I tried several times using the TRIM function. I was having trouble with this because it would often return the text equilvalent of the date, it would not show the date. I did try the replace idea, it seemed to work the best and removed all the spaces and left the dates in place - Thanx! -- Ltat42a ------------------------------------------------------------------------ Ltat42a's Profile: http://www.excelforum.com/member.php...o&userid=24735 View this thread: http://www.excelforum.com/showthread...hreadid=391784 |
ASAP Utilities, a free Add-in, available at www.asap-utilities.com has a
feature that does this nicely. Vaya con Dios, Chuck, CABGx3 "Ltat42a" wrote in message ... We have a personnel roster that is kept in a database. Occasionally, this roster is exported into Excel and sent out to members of the department. In one column are dates that each person was employed. When I try to sort the entire roster, it doesn't come up right because most of the cells that contain the date have spaces in front of it. Sometimes it's 1 or 2, sometime more, other times there are no spaces. In order for me to sort the entire roster correctly, I'm having to manually remove the spaces in front of the date, then sort the roster according to date. There are over 500 entries in this roster, is there a better way to remove the spaces other than manually? Thanx... -- Ltat42a ------------------------------------------------------------------------ Ltat42a's Profile: http://www.excelforum.com/member.php...o&userid=24735 View this thread: http://www.excelforum.com/showthread...hreadid=391784 |
Thanx again for the replies, as for using macros, I get a whole new spreadsheet every so often, so, I'd have to create the macro just so I can run it. For this, the find/replace would work just fine. -- Ltat42a ------------------------------------------------------------------------ Ltat42a's Profile: http://www.excelforum.com/member.php...o&userid=24735 View this thread: http://www.excelforum.com/showthread...hreadid=391784 |
| All times are GMT +1. The time now is 05:04 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com