A function to change "text" to "numbers" so the result can be calculated.

Hi,

I have a problem with a huge set of data in a quite variable text format in Excel. I have been able to get the data to look like a normal mathematical function with a bunch of functions. Now I have cells which for example say: "=500/1000" (without the quotation marks). But I haven't found a way to calculate these values. I know I could do it by copying the cells and then paste special using the "values" selection, but that does not satisfy me as it will be very tedious as there's a lot of data and in many different sheets. Also that method requires me to double click each cell and then press enter for Excel to calculate it. I am looking for a way to make it fully automatic, so I could just make the calculations work without any need for any manual work.

Is there a way to solve this? Preferably without VBA as I do not understand anything about those.

Thanks.

Hmm. So far, it's only division ("/"), but I guess I could change it to multiplication also without much problem, if that would help. This link has an example sheet with what I'm talking about: https://www.dropbox.com/s/jdjds82ez3...readsheet.xlsx

Originally Posted by twiggywales

if they are always this set up then this will work

Please Login or Register  to view this content.

there may be a better way but this will work

Very nice! Thank you! I do not understand what that does exactly, but it does seem to do the trick! Thank you. If you care to explain the function in a bit more detail, I would much appreciate it, so I would actually learn to solve such problems better by myself.

Can you not use the text to column tool/command?

Thank you! It seems that there is no easy function for this, but your functions make it work, even if it produces a damn long formula, especially as I already had a rather long formula to get where I am currently. Anyway, it works and that's the most important thing. Thank you again.