I'm getting a formatted amount of money from a web service. It can be in different formats and using different currencies, e.g.
$ 1.10
€ 1,10
1,10 €
EUR 1.10
(perhaps, I'm not sure I'll actually encounter this one)I would like to extract the currency symbol ($
) from it, and if possible get the associated Currency
object (Java) from that symbol. I don't need to extract the amount, I can get that somewhere else.
Extract the text string representing the full custom number format using the [cell]. numberformat property (gives something like “CAD” #,##0.00;”CAD” -#,##0.00″) Trim the text string (MID) to remove the everything except the currency code. Return the currency code as a string to the spreadsheet.
On the Home tab, click the Dialog Box Launcher next to Number. Tip: You can also press Ctrl+1 to open the Format Cells dialog box. In the Format Cells dialog box, in the Category list, click Currency or Accounting. In the Symbol box, click the currency symbol that you want.
Here's how to convert currency in Google Sheets to get the exchange rate of dollars to the three currencies in column B: Select the first cell of the column where you want the results to appear (C2). Type the formula: =GOOGLEFINANCE(“CURRENCY:USDINR”) Press the return key.
you can use a regular expression, to parse your result from the webservice. You have to filter all characters, except numbers, dots and whitespaces. Here is the regex for that:
String regexp = "[^0-9\\.,\\s]*";
The first group of the matching result is the currency symbol (or the name e.g. EUR).
Here is my sample method:
public void test() throws Exception {
String text = "2.02 $";
String regexp = "[^0-9\\.,\\s]*";
Pattern p = Pattern.compile(regexp);
Matcher m = p.matcher(text);
while (m.find()) {
for (int i = 0; i < m.groupCount() + 1; i++)
LOG.info(m.group(i));
}
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With