Find 1 word and replace it and leave the leading zero

I have a report with sub-totals in Excel 2003 and I have copied the
sub-totals as a paste special values to a new spread sheet and want to remove
the word total and keep a leading zero that is part of our Part Numbers. is
there an easy way to do that? Here's an example cell
Before:
03010042 Total
After I want this:
03010042
When I do a find and replace it removes the leading 0 even with the column
set as a text column.

Hi,

Format the cell after you have replaced the contents:

Format => Cells => Custom, then in the Type bar enter "00000000" (without
the quotation marks)

Ewan

"Leslie" wrote:

> I have a report with sub-totals in Excel 2003 and I have copied the
> sub-totals as a paste special values to a new spread sheet and want to remove
> the word total and keep a leading zero that is part of our Part Numbers. is
> there an easy way to do that? Here's an example cell
> Before:
> 03010042 Total
> After I want this:
> 03010042
> When I do a find and replace it removes the leading 0 even with the column
> set as a text column.
>

format the selected area as TEXT first and then use.
Sub removetextleaveleadingzero()

For Each c In Selection
c.Value = Left(c, Len(c) - 5)
Next

End Sub

--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"Leslie" <Leslie@discussions.microsoft.com> wrote in message
news:368FBC6D-17A3-490B-A198-DB7679D30963@microsoft.com...
>I have a report with sub-totals in Excel 2003 and I have copied the
> sub-totals as a paste special values to a new spread sheet and want to
> remove
> the word total and keep a leading zero that is part of our Part Numbers.
> is
> there an easy way to do that? Here's an example cell
> Before:
> 03010042 Total
> After I want this:
> 03010042
> When I do a find and replace it removes the leading 0 even with the column
> set as a text column.
>