Avatar of vegas86
 asked on

Using Find and Replace in Excel 2010


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?
Microsoft Excel

Avatar of undefined
Last Comment

8/22/2022 - Mon

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.


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
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.

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...

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.

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...
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.

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.

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question

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

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

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes

anytime ...glad I could help