How do I paste a number, stripping out text or currency marker (eg: "1234 USD")?

[Flimm]

I’m using LibreOffice Calc. I would like to copy and paste some values from a Google Sheets spreadsheet in my browser. The Google Sheets spreadsheet looks like this:

Item	Amount
1	USD $55.00
2	USD $30.00
3	USD $45.00

The “Amount” column contains the numbers 55, 30 and 45 formatted as a currency with the text “USD” prefix. If I select these cells in Google Sheets, copy them and paste them to a spreadsheet in LibreOffice Calc, I am shown a dialog with the title “Import Options”:

Whether or not I check the checkbox “Detect special numbers (such as dates)”, the numbers get imported into the LibreOffice Calc spreadsheet as text, with the prefixes “USD” included. I would like these numbers to be imported as numbers, so that I can do calculations on them, such as SUM. How do I do that?

[Villeroy]

“USD $55.00” has 2 currency designators USD and $.

Edit>Paste-special… [Ctrl+Shift+V]
Import unformatted text.

Choose English(USA) as language because you do import US currency.
Make sure that the preview shows 3 columns 1 | USD | $55.00. You may have to add “Space” as delimiter. I can’t know the content of your clipboard.
Check “Detect special numbers” because the text “$55.00” is a special number.

Now you import real numbers with the USD strings separated from the currency values. Select the cells and reset the formatting (Ctrl+M) or apply any number format you want.

[Flimm]

Thanks, that helped. I had to change the language to English (USA), as you said, leaving it at English (UK) did not work.

For some reason, I couldn’t get it to recognize space as a separator. If I selected that option, the number would be deleted, and only “USD” would remain. Instead, I used “Fixed Width” and put the “USD” suffix in a fixed width column of its own.

I did have to check “Detect special numbers” as you said.

Once I did all this, the second column was imported as numbers formated as currency. Because my locale is English (UK), the currency was formatted like £55 , but that can be fixed by changing the formatting. Using SUM and other mathematical calculations worked.

[Flimm]

Here’s one solution. Copy and paste the numbers into your spreadsheet. At this point, you have a few cells that are formatted as text with text values like USD $55.

Select these cells, and click “Edit” menu, then “Find and Replace…”. In the “Find” field, enter USD $. Leave the “Replace” field blank. Under “Other options”, make sure the checkbox “Current selection only” is checked". Then click “Replace All”. The cells will be considered numbers once they only contain digits.

If the numbers contain a thousands separator, you may have to find and replace all the thousands separators too.

[Villeroy]

Yes, replacing “USD $” with nothing fixes things IF you are working with an English locale. With a comma locale (e.g. German, French and most other locales from continental Europe) I have to change the number format locale of the affected cells, so they treat the point as decimal separator.
Fixing wrongly imported data has more pitfalls than the text import dialog. Think of 31/12/1999 dates versus 12/31/1999 where you get a mixture of text and wrong dates with twisted months and days when you do it wrongly. Or decimals like 23.123 which would be imported as 23,123 with my German locale