I have a Google Sheets cell with content like this: £5,300.23 and I can't find a simple way to reformat it as numbers. My current solution is in three steps: remove the currency sign ( = substitute(A1, "£", "") ), paste-special into a new column, then format the column as 'number'. Is there a better way?

Reputation: 41

Simpler way to reformat text as numbers in Google Sheets

I have a Google Sheets cell with content like this: £5,300.23 and I can't find a simple way to reformat it as numbers.

My current solution is in three steps:

Is there a better way?

Upvotes: 0

Reputation: 1

you can do simply:

=SUBSTITUTE(A18, "£", )*1

and arrayformula would be:

=ARRAYFORMULA(SUBSTITUTE(A18:A20, "£", )*1)

Upvotes: 0

Reputation: 4382

Use Value on the result of substitute:

=VALUE(SUBSTITUTE(A1,"£",""))

Upvotes: 1