CODEWITHSUNDEEP
excelexcel-formulavba
ZRS
ZRS

Reputation: 23

Formula to display number format and text together

My cell A2 value is 1512215.22USD (text format, no commas or points), I need a formula to convert (can also be in text format) in B2 as 1,512,215.22 USD. The value may vary between .01 to 100,000,000.00 maximum.

I also wonder possible to execute an excel event when A2 is selected to apply in A2 while entering the value.

Thanks in advance

Upvotes: 1

Views: 307

Answers (2)

CLR
CLR

Reputation: 12279

This function ought to do it - just use it in the event but make sure you disable events before you write the response to the cell and enable them again after or it will loop.

Function formatmoney(unformatted As String) As String
    Dim numberpart, currencypart As String

    numberpart = Left(unformatted, Len(unformatted) - 3)
    currencypart = Right(unformatted, 3)
    formatmoney = Format(Val(numberpart), "#,##0.00") & " " & currencypart
End Function

Upvotes: 0

Mike Powell
Mike Powell

Reputation: 260

Assuming this will only be USD ... enter image description here

Upvotes: 1

Related Questions