Need help querying column and manipulating data

[Epwna]

Hello all,

This is probably a very basic question, but I am new to Excel.

I am pulling information from a finance source (Yahoo! Finance) into a spreadsheet and need to parse the data a little bit.

Yahoo reports income statement figures for companies in billions of dollars (i.e. "1.45b" is the way it is written after import). I need to convert this data to millions if it is in billion or leave it in millions if it's not...so convert 1.45b to 1450 and keep something like 987.7m as 987.7 to then use in another spreadsheet.

I have been doing this by hand by having a separate column dedicated to multiplying every billion figure by 1000 to get millions. Obviously this isn't the ideal way of doing this.

Can I somehow query that entire column for figures that contain "b" OR "m", and if the figure contains "b", strip the "b" out of it, multiply it by 1000 and copy to the next column? Similarly if a figure contains "m", just strip the "m" and copy to next column.

I hope this makes sense...

Thanks in advance!

[Cutter]

Maybe

=IF(RIGHT(A1,1)="m",A1,LEFT(A1,LEN(A1)-1)*1000&"m")

If you don't want the "m" attachment then

=LEFT(A1,LEN(A1)-1)*IF(RIGHT(A1,1)="b",1000,1)

[Epwna]

Maybe

=IF(RIGHT(A1,1)="m",A1,LEFT(A1,LEN(A1)-1)*1000&"m")

If you don't want the "m" attachment then

=LEFT(A1,LEN(A1)-1)*IF(RIGHT(A1,1)="b",1000,1)

You are the master! THANK YOU!

[Cutter]

You're welcome. Don't forget to mark your thread as SOLVED.