list of prices in multiple currencies

[MartinP]

I download lists of prices (“open”,“buy up to” , “latest”) in multiple currencies. These are in the form HK$73.55, 12.32p, $25.00, C$12.23, ¥2353.00, etc.

Before being able to calculate anything I want to separate off the currency and the price in numeric form and put them into separate columns.
The problem is the variability of where the currency symbol is placed.
HK$ (Hong Kong) is 3 characters in front of the number, $ (USA) is 1 character in front of number, p (UK) is 1 character at the end of the number.

Two questions
is there a function aleady available which would do what I want?
If not how would I go about creating one?

[LeroyG]

To put each data in separate columns, select the data cells, choose menu Data - Text to Columns…, and type , (comma) in the Other field, and check Trim spaces.

Now you can Find All (HK$, C$, $, ¥, p, etc. by turns; leave $ for last, after all other combinations with $), Replace All (by nothing), and format remaining selected cells as currency. Once for each currency.

A macro could be helpful, for not to repeat all every time you download a new list.

---

Add Answer is reserved for solutions. If you think the answer is not satisfactory, add a comment below, or click edit (below your question) to add more information. Thanks.

[MartinP]

The find/replace approach you specify is basically what I’ve been doing everytime I download a new set of prices. I guess I’ll just have to create a macro.
Thanks LeroyG

[LeroyG]

Can any high-karma user delete my first comment (“Maybe you can Find…”), please? Thanks.

[EarnestAl]

Done. I didn’t think I could. Cheers, Al