Forum Discussion
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,...
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.
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,
Paul
What do you mean with a base connection? Is that a possibility to detect the cell formatting?
Best,
Paul
Power 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.
