Assuming
- amount in cell A1
- a currency string prefix
- Decimal separator “.”
- Thousands separator “,”
- For example “S$4.86” or “US$1,234.56”
use
=NUMBERVALUE(RIGHT(A1,LEN(A1)-SEARCH("$",A1,1)),".",",")
Or in LibreOffice you can first simply remove all characters which are not a digit or “.” using a regular expression such as
=NUMBERVALUE(REGEX(A1,"[^0123456789\.]","","g"),".")
or
=NUMBERVALUE(REGEX(A1,"[^\d\.]","","g"),".")
Note in practice you would write this formula once then move or copy it to other locations, that way your spreadsheet program will update the multiple A1 references for you, for example
- have a copy of the above formula which applies to the cell below
- copy it to the cell above a column you need the number value
- move the formula to the column you want the result in
- copy down