Link to home
Start Free TrialLog in
Avatar of vegas86
vegas86

asked on

Using Find and Replace in Excel 2010

Hello,

Does anyone know how to use find and replace in excel to do the following?

I have formulas in column K, they are all the same so eg "=35.37*K347"

what I would like is to use find and replace to find all the formulas and replace with just the value it is multiplying by so in this example 35.37

The thing is though the formulas are referencing different rows and some have difference values, so not always 35.37. Its always column K though and always * formula.

Does find and replace work like this or should I give up?
Avatar of SeaSenor
SeaSenor
Flag of United States of America image

if you search for   *K    then replace with =xxx*k  then it should work   (xxx being the numbers you want instead of 35.37 for example)

try a few cells for test.
Avatar of vegas86
vegas86

ASKER

Hi,

I just tried it and it didn't work. I need the end result to be just the number value eg 35.37 without any of the formula. So I guess I am saying I want  to remove all the formula.
then just use the number in the replace field
hold on.. I think I understand what you are wanting now.

you want to leave the existing 'multiplier' in place and remove the surrounding formula?

can you explain your objective?  There may be another way to achieve it besides find and replace...
Avatar of vegas86

ASKER

yes, so for example the formula is =35.37*K9 and K9 has 30 in it the value in the cell is 1061.1. I want the value in the cell to show as 35.37 and nothing else.
ok..
is it possible for you to change all the K cells to a number one?

if so, do that, then highlight all the cells you want to remove the formula from.

then 'copy' and paste special, and choose 'values'

If you don't want to keep the K cells as ones, you can 'copy' then undo, then paste values...

then you can save the workbook.

make sense?

you might make a copy of the workbook as a 'test' copy and play with it.
Once you get it like you want, then manipulate the original file.

This should work though...
Avatar of vegas86

ASKER

If I copy and paste values though I get the end result value which is 1061.1, I need the value in which I am mulitplying by not the end result.
ASKER CERTIFIED SOLUTION
Avatar of SeaSenor
SeaSenor
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of vegas86

ASKER

oh I get you now!! sorry!! I read it as changing the cell reference to 1! I will give it a go!
Avatar of vegas86

ASKER

This works!! thank you so much for your patience with me!

:)
anytime ...glad I could help