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 24, 2020

    Dear SergeiBaklan,

     

    I am afraid I was not quite clear. Starting position can be found in merely the values in cells A4:A11. The currency code can only be found in the cell notation, behind CTRL-1.

    All other columns were manually entered by my self to explain my problem.

     

    I want to read the currency code in cells A4:A11 with a formula or with Power Query.

     

    Do you have a solution?
    Paul

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      May 24, 2020

      pkogels_acsi_eu 

      Paul,

       

      You may add custom function (UDF)

      Function get_format(r As Range) As String

get_format = r.NumberFormat

End Function

      and use it in formula

      = TRIM(
   IF( LEN( SUBSTITUTE(SUBSTITUTE(get_format([@Amount]),"General",""),"""","")),
          SUBSTITUTE(SUBSTITUTE(get_format([@Amount]),"General",""),"""",""),
          "EUR")
)

      Convert currency with using French locale easier in Power Query.

       

      • pkogels_acsi_eu's avatar
        pkogels_acsi_eu
        Copper Contributor
        May 25, 2020

        Dear Sergei Baklan,

         

        Thank you for your help. I have already tried another solution (UDF as well) succesfully.

        However, your basic function for getting the cell format will do as well.

        Thank you for your help!

         

        Paul

Resources