Converting text into numbers

Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 09:21 PM GMT

Question

I recntly had extracted data from google finance and I have to do work on it with the numbers using2007. I have come to a problem where the numbers are not actually stored as numbers but they are like 12.3B and 456M in the market capitalization column where it cannot be processed. I have to change back the 12.3B and 456M into 12300000000 and 456000000 respectively. I know that actually I could do it with CTRL+H and paste special. But the problem is I have to do it in vast amound like 60 spreadsheets while some of them is in Chinese and some in English. Do you have any idea how to do it?

I do have some experience in programming. So if you could show me a VBA program which works and which attributes to change, particularly the attribute to change source of thelike 'B for Billion' and the target like 'multiply 100000000 in the target box', for different languages that would be fine.

Thanks for your help :)

Answer

Isn't the easiest way to do a search and replace - select the entire column, replace B with the appropriate number of zeros, then do the same for M - job done?! This could easily be done using a macro as well - simply test each range of cells and use the substitute function to replace the text you don't want.

question found in the question pool

Advertisement

©2017 eLuminary LLC. All rights reserved.