Forum Discussion

pkogels_acsi_eu's avatar
pkogels_acsi_eu
Copper Contributor
May 24, 2020
Solved

Detect cell notation with formula or Power Query

Hi, In a sheet with booking information, the columns with amounts a formatted with special notations, indicating the currency of the amount. In the cell itself, only the value is stored. On screen,...
BI & Data Analysis
excel
Formulas and Functions
  • Subodh_Tiwari_sktneer's avatar
    Subodh_Tiwari_sktneer
    May 25, 2020

    pkogels_acsi_eu 

    No problem! Please replace the UDF with the following one.

     

    Function getCurrencySymbol(AmtCell As Range) As String
Dim Matches As Object

With CreateObject("VBScript.RegExp")
    .Global = False
    .Pattern = "General ""([A-Z]{3})"""
    
    If .test(AmtCell.NumberFormat) Then
        Set Matches = .Execute(AmtCell.NumberFormat)
        getCurrencySymbol = Matches(0).SubMatches(0)
    Else
        getCurrencySymbol = "EUR"
    End If
End With
End Function

     

    If that takes care of your question, please accept the post with the proposed solution as a Best Response to mark your question as Solved.

     

     

     

pkogels_acsi_eu's avatar
pkogels_acsi_eu
Copper Contributor
May 25, 2020

Dear Subodh_Tiwari_sktneer,

 

Thank you for your help so far. 

With one small change, the user defined function would be perfect: if no special formatting has been set, the currency code should be seen als EUR.

Could you add that?

If not, I can set the currency column to EUR if no value has been calculated with the UDF.

 

Looking forward to hear from you again,

 

Paul

Subodh_Tiwari_sktneer's avatar
Subodh_Tiwari_sktneer
Silver Contributor
May 25, 2020

pkogels_acsi_eu 

No problem! Please replace the UDF with the following one.

 

Function getCurrencySymbol(AmtCell As Range) As String
Dim Matches As Object

With CreateObject("VBScript.RegExp")
    .Global = False
    .Pattern = "General ""([A-Z]{3})"""
    
    If .test(AmtCell.NumberFormat) Then
        Set Matches = .Execute(AmtCell.NumberFormat)
        getCurrencySymbol = Matches(0).SubMatches(0)
    Else
        getCurrencySymbol = "EUR"
    End If
End With
End Function

 

If that takes care of your question, please accept the post with the proposed solution as a Best Response to mark your question as Solved.

 

 

 

Resources