Replacing half a formula

[Guest]

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

 

[Guest]

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.

 

[Gord Dibben]

One method if these are in one column.

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

Select the column and Edit>Replace

what: =

with: ^^^

Replace all

Data>Text to Columns>Delimited by>Other * >Next>Select second column and "do not
import" then finish.

Edit>Replace ^^^ with =


Gord Dibben MS Excel MVP

 

[Gord Dibben]

Good solution Vergel

Beats hell out of Edit>Replace method.


Gord

 

[Guest]

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.