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.

     

     

     

Subodh_Tiwari_sktneer's avatar
Subodh_Tiwari_sktneer
Silver Contributor
May 25, 2020

pkogels_acsi_eu 

 

I used a Custom Defined Function to fetch the currency symbol used in custom formatting of the amount cells.

 

Please refer to the Blad2 in the attached, the UDF is used as a worksheet function in the yellow column of the table. Let me know if this is what you were trying to achieve.

 

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)
    End If
End With
End Function

 

 

 

 

  • 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