Formatting data

Visitor

Hi,

So I need to change how my data is formatted and i don't really know how to do it. The data I have is volume and some of it is in millions and some in billions. An example of how it looks is 100M or 100B. I need my data to be in number form, e.g. 100,000,000 or 100,000,000,00. I have found methods online of going from number form to letter form, but I can't find a method of how to go from letter form to number form and don't really know enough about excel to be able to 'reverse' the methods I've found online. If anyone could help, it would be greatly appreciated.

Thanks!

1 Reply

@Rowtom 

A value such as  100M is a text value. Formatting won't help, but you can use a formula to convert the value to the corresponding number.

With such a value in A2, use the following formula in (for example) B2:

 

=IF(RIGHT(A2)="K",1000*LEFT(A2,LEN(A2)-1),IF(RIGHT(A2)="M",1000000*LEFT(A2,LEN(A2)-1),IF(RIGHT(A2)="B",1000000000*LEFT(A2,LEN(A2)-1),A2)))

 

This can be filled or copied down.

 

S0584.png