Excel - modifiying downloaded data.?

Question

Need to take numbers like 495B and 444M and make them into numbers I can do calculations with. Any suggestions? There are over 800 data points. I don't want to manually go in and delete the B or M from every cell

Answer 1

1. highlight the cells with suffix M & B
2. ctrl+H for replace, check match case box, 1st replace M with 000000, click replace all button
3. ctrl+H again, replace B with 000000000, click replace all button.
4. you will end up with exponent value like 4.95E+11, while it is still highlighted, menu, format, cells, number tab, choose the number type you like.
Hope this would help.

Answer 2

You can convert the cells to numbers with a formula. Assume your data is in column A. In B1 enter the following formula:

=IF(RIGHT(A1,1)="B",VALUE(LEFT
(A1,LEN(A1)-1))*1000000000,
IF(RIGHT(A1,1)="M",
VALUE(LEFT(A1,LEN(A1)-1))
*1000000,"Error"))

Note the above is shown on 5 lines to display here. Enter the whole formula in the formula bar for cell B1.

The formula looks at the right-most character of A1. If it's a "B" it takes the text to the left of the last character, converts it to a number and multiplies it by 1 billion. If the right-most character is an "M" it does the same thing but multiplies the number by 1 million. If the right-most character is neither a "B" nor an "M", the formula will display "Error".

After you've entered the formula in B1, copy and paste down from B2 through Bn, where n = the last row of your input data, or approx. 800 rows per your question.

Answer 3

Select the whole column, Select data, text to columns menu, Click fixed width, separate letters and numbers in one go. Work if they are all the same width eg 4 characters in your case.

if variable width and the letters are single appended. Use
=left(a1,len(a1)-1)*1.