Removing spaces in cells with data in it

[Ltat42a]

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...

[swatsp0p]

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...

[Darlene]

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
>
>

[carrie]

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
>
>

[Ltat42a]

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!

[David McRitchie]

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

[CLR]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Ltat42a]

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.