I have a rows of formulas of 150 lines
ex: =Takeoff!B6*D7
=Takeoff!B54*D8
=Takeoff!B102*D9
I need to take off the *D7,*D8,etc is there a formula I can put in the find
and replace to do this easier than going to each cell and deleting it.
Or is there another way using Text replace item?

Thanks
david

Maybe this would help:

You need to make sure that there's a 'spare' column to the right of the
column where you want the 'search and replace' to happen.

1. Tools-Options, in the View tab, check Formulas.
2. You should now see the formulas. Select the entire column where you want
to 'search and replace'
3. Data-Text to Columns
3.1 Click on Delimited. then Next.
3.2 Check Other, then type * in the text box to its right. Click on Finish.
4. The column to the right should now have D7, D8, D9, etc. Delete this
column.
5. Tools-Options, in the View tab, uncheck Formulas.


"dpal" wrote:

I have a rows of formulas of 150 lines
ex: =Takeoff!B6*D7
=Takeoff!B54*D8
=Takeoff!B102*D9
I need to take off the *D7,*D8,etc is there a formula I can put in the find
and replace to do this easier than going to each cell and deleting it.
Or is there another way using Text replace item?

Thanks
david

One method if these are in one column.

Note sure though with the "rows of formulas with 150 lines" statement.

Select the column and EditReplace

what: =

with: ^^^

Replace all

DataText to ColumnsDelimited byOther * NextSelect second column and "do not
import" then finish.

EditReplace ^^^ with =


Gord Dibben MS Excel MVP


On Sat, 24 Feb 2007 10:38:03 -0800, dpal wrote:

I have a rows of formulas of 150 lines
ex: =Takeoff!B6*D7
=Takeoff!B54*D8
=Takeoff!B102*D9
I need to take off the *D7,*D8,etc is there a formula I can put in the find
and replace to do this easier than going to each cell and deleting it.
Or is there another way using Text replace item?

Thanks
david

Good solution Vergel

Beats hell out of EditReplace method.


Gord

On Sat, 24 Feb 2007 10:59:08 -0800, Vergel Adriano
wrote:

Maybe this would help:

You need to make sure that there's a 'spare' column to the right of the
column where you want the 'search and replace' to happen.

1. Tools-Options, in the View tab, check Formulas.
2. You should now see the formulas. Select the entire column where you want
to 'search and replace'
3. Data-Text to Columns
3.1 Click on Delimited. then Next.
3.2 Check Other, then type * in the text box to its right. Click on Finish.
4. The column to the right should now have D7, D8, D9, etc. Delete this
column.
5. Tools-Options, in the View tab, uncheck Formulas.


"dpal" wrote:

I have a rows of formulas of 150 lines
ex: =Takeoff!B6*D7
=Takeoff!B54*D8
=Takeoff!B102*D9
I need to take off the *D7,*D8,etc is there a formula I can put in the find
and replace to do this easier than going to each cell and deleting it.
Or is there another way using Text replace item?

Thanks
david

Thanks, Gord. You have a very good point as well in that the unneeded
columns can be ignored in the 3rd screen of Text-to-Columns. Thank you for
that learning.


"Gord Dibben" wrote:

Good solution Vergel

Beats hell out of EditReplace method.


Gord

On Sat, 24 Feb 2007 10:59:08 -0800, Vergel Adriano
wrote:

Maybe this would help:

You need to make sure that there's a 'spare' column to the right of the
column where you want the 'search and replace' to happen.

1. Tools-Options, in the View tab, check Formulas.
2. You should now see the formulas. Select the entire column where you want
to 'search and replace'
3. Data-Text to Columns
3.1 Click on Delimited. then Next.
3.2 Check Other, then type * in the text box to its right. Click on Finish.
4. The column to the right should now have D7, D8, D9, etc. Delete this
column.
5. Tools-Options, in the View tab, uncheck Formulas.


"dpal" wrote:

I have a rows of formulas of 150 lines
ex: =Takeoff!B6*D7
=Takeoff!B54*D8
=Takeoff!B102*D9
I need to take off the *D7,*D8,etc is there a formula I can put in the find
and replace to do this easier than going to each cell and deleting it.
Or is there another way using Text replace item?

Thanks
david