How to remove currency-related prefixes in spreadsheet?

[sphoenix]

I need help with removing all currency-prefixes of prices in a column of historical price spreadsheet downloaded from mutual fund companies. Without such post-processing of price information, calculation cannot be directly performed because prices are not strictly numerical but prefixed by currency type. Example is USD 100 is not recognised as value; to get Excel do calculation on USD 100, I need to remove USD to get 100.

[RobotRising]

Do you have a few examples rows that you need to modify?

[sphoenix]

 

[RobotRising]

Are all of the prefixes 3 Characters at the start of that column?

[sphoenix]

I am hoping for some AI to scan all columns and determine column type of which price in my example is all cells are same containing numerical prefixed by characters. I haven’t spent time researching more possibilities of spreadsheets from mutual fund institutions to narrow down what a price column look like. 

[efialttes]

Hi @sphoenix 
Are you working with Power Automate Desktop... or with Power Automate Cloud and Excel connector, action block 'List rows from a table'?
If Power automate Cloud, you can manipulate easily the content of a column by means of 'Select' action block.
Assuming the name of the column is NAV, I would use the following expression to remove the 4 first characters from a string and convert the result into a float:

float(skip(item()?['NAV'],4))

 Just remember to add the rest of the Excel columns asextra rows in the 'Enter key / Enter Value' map table, so the 'Select' output will contain the same info as the input Excel table

More info on the 'Select' action block here:
https://learn.microsoft.com/en-us/power-automate/data-operations#use-the-select-action

In case you are using 'Power Automate Desktop' I guess you will need to implement a loop in order to perform the manipulation. If so, good stuff hre:
https://powerusers.microsoft.com/t5/Power-Automate-Desktop/Update-datatable/m-p/1286967/highlight/tr...

Hope this helps
==========

[sphoenix]

Thanks but i still have to learn substantial coding work before I can get spreadsheet cleaned up on prefixes. I thought AI exist ( learn from template ). Also, I am doing this for reporting monthly market value of my portfolio to tax authorities which doesn’t generate income from such coding.

On the other hand, I have raised my issue to Morningside institution ( assuming there are other non-coders who need to do calculations on download of historical prices).

I have chosen Apple ipad instead of Microsoft hardware (albeit more powerful) due to security concerns based on online advise.

[RobotRising]

I would actually recommend doing your data cleaning on the Excel side instead of Power automate. You can use the replace or right function in your excel columns that have currency and remove the first three characters

https://www.ablebits.com/office-addins-blog/remove-first-last-characters-excel/

Although it sounds from your last post that you don't know which column contains currency which will make this more difficult from either the Excel side or Power Automate side as every column will need to be conditionally checked. 

[Chriddle]

I'm assuming you don't need to do anything there ‌‌
When you format an Excel cell as “currency,” the symbol Excel adds is not part of the value.

[RobotRising]

It sounds like they aren't formatted as currency but the text is just added to the field by the reporting system these are being pulled from but I could be wrong 🙂

[sphoenix]

I am also hoping it’s easier than just formatting with appropriate currency but unfortunately not.

[sphoenix]

I am also hoping it’s easier than just formatting with appropriate currency but unfortunately not. Maximum function is not recognising formatted cells with currency

[sphoenix]

After reformatting, I am not getting calculations.