How to convert values from "£#m" to "£#,###,###" & "£##k" to "£##,###"

[#1]

Hello Community.

I am working with some data at the moment which has 'TransferFee' values stored as £5.50m, £45k, £560k, £55.69m, £2m etc, and I'd like to understand the process of how to convert these values into Standard numbers e.g £5.50m to £5,500,000, £40k to £40,000.. As the data is currently stored in a text format (as it contains M's and K's), I'm unsure of what to do.

I imagine that I need to manipulate the data in the script during the loading phase ?

Any help will be appreciated

Thanks

Liam

[m_woolf · Accepted solution]

if(right(TransferFee,1) = 'm', purgechar(TranferFee,'£m')*1000000,

     if(right(TransferFee,1) = 'k', purgechar(TranferFee,'£m')*1000)) as TransferAmt;

This should result in the correct value without any currency symbol included.

You may need to wrap in num().    

[#3]

Thank you very much for your swift response, this worked a treat !!!