I have something like "9/12/03 Joe Average" in several
cells (dates and names are different of course). I would
love to be able to write a macro or routine that
eliminates everything after the first space, leaving only
the date.

I can definitely do this the "long way" by inserting an
empty column and then combining the MID and LEN functions,
then using copy, paste special (values) and then deleting
the original column.

However, I just thought there might be a quicker way by
using VBA code by using something like:

ActiveCell.Characters.Delete

But I guess I would need some sort of MID and LEN formula
in there to help me know WHAT characters to delete. Is
this method worth exploring?? Can anyone assist?

Thanks in Advance,
Don

Don

this routine will replace the contents of the cell, A1 in this case, by the
information up to the space.

You'll need to put together a loop if there's more than one cell. Something
like:

Sub RemoveStuffLoop()
Dim Cell As Range
For Each Cell In Selection
Cell.Value = _
Left(Cell, _
Application.WorksheetFunction.Search _
(" ", Cell) - 1)
Next 'cell
End Sub

There are other ways of processing all the cells in a range without
selecting it ... but you'll find lots of examples of that in the archive.
And you might need some error checking in case there are cells without a
space in them or, in this case, to check that you have selected some cells.

Regards

Trevor


"Don" wrote in message
...
I have something like "9/12/03 Joe Average" in several
cells (dates and names are different of course). I would
love to be able to write a macro or routine that
eliminates everything after the first space, leaving only
the date.

I can definitely do this the "long way" by inserting an
empty column and then combining the MID and LEN functions,
then using copy, paste special (values) and then deleting
the original column.

However, I just thought there might be a quicker way by
using VBA code by using something like:

ActiveCell.Characters.Delete

But I guess I would need some sort of MID and LEN formula
in there to help me know WHAT characters to delete. Is
this method worth exploring?? Can anyone assist?

Thanks in Advance,
Don