Convert formula into value

[dhd2005]

Hi all,

I'm trying to convert the output of formulas in a range of cells (B2:B160) into a number. I'm familiar with a couple of ways to do this, but I'm trying to avoid the Paste -> Paste Special way.

I know that you can hit F2, then F9 in each cell to instantly convert, but does anyone know how to apply that method to a range of cells? Hitting two buttons 159 times in each column is something I'd like to avoid.

Thanks in advance!
David

[Richard Buttrey]

Hi,

Why are you trying to avoid the Ctrl-c and then Alt E S V key strokes? Other than a VBA macro I can't think of any other way.

Regards

[darkyam]

To add to what Richard is saying, a macro might very well be slower, will be more prone to breaking, and, should you execute the macro accidentally, you can't undo it. Copy/Paste Special is the best option.

[dhd2005]

Hi all,

Thanks for the responses. My problem is that I'm trying to convert values as part of a macro. Specifically:

Column A contains text. Columns B & C contain formulas that generate numbers based on that text (both via LOOKUP function). Columns D & E need to generate values based on the numbers in B & C. However, since both B & C are formulas, the formulas in D & E don't work.

I've attached a simple example. I can just copy the data in Columns B & C, and then Paste -> Special -> Values, but then I also have to hit the "Convert to Numbers," pop-up. I'd be happy to include that in the macro, but it never carries the conversion over (just the pasted values, which then need to be converted to number).

Any suggestions?

Thanks,
David

[Dav]

It is not so clear what you wish to acheive, but items from the lookup functions, I assume you want the numbers to be numbers and not text.
Try puting the lookups like =LOOKUP(A2,{"Bread","Eggs","Milk"},{1,3,5}) losing the quotes round the numbers. Does this give you what you want?

Your if statements also do not contain a result for if the statement is false and so return a FALSE

Why not give an example showing the results you want to appear, so we can see what you are trying to acheive.

Regards

Dav


You will see the numbers are treated as text as they are right aligned.

If for some reason they have to be returned as numbers

[dhd2005]

Hi Dav,

Removing the quotation marks actually accomplished what I needed. From there, I can copy & Paste Special -> Values, and then there's no need to convert.

Thank you so much for your help! You're a lifesaver.

-David