Forum Discussion
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, an amount of 100 may be presented as 1040 GBP or 1040 CHF, because of the cell notation Standard "GBP" or Standard "CHF".
See attachment: daily I get booking amounts like stored in cell A4:A11.
With what formula or what Power Query function can the cell notation be detected? The CELL() function falls short, because of the lack of arguments for special notations.
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 FunctionIf that takes care of your question, please accept the post with the proposed solution as a Best Response to mark your question as Solved.
12 Replies
Until recently, I would create my Base Connection, then I'd do the following to create the new query to reference that one.
Go to the Power Query tab.
Show the Workbook Queries pane.
Right-click the Base Connection query and choose Reference.
- Dear Mr. Smith.
What do you mean with a base connection? Is that a possibility to detect the cell formatting?
Best,
PaulPower Query takes from the source only values, not properties. In case of Excel only cell values, not cell properties like color or format. That's VBA to work with properties as well.
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 FunctionDear 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
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 FunctionIf that takes care of your question, please accept the post with the proposed solution as a Best Response to mark your question as Solved.
Can you upload a sample workbook along with the desired output mocked up manually?
You're the first and only one to reply. Please find the attachment.
Looking forward to a solution,
Paul Kogels
