Currency Format

[Guest]

We have a formula that extracts numbers from a text field. Thse are currency
amounts. Is there a formula that can put his into a number format?

'=IF(LEFT($E2,3)="CAD",RIGHT($E2,LEN($E2)-3),0)

Thank you!

 

[Dave Peterson]

Right will return text.

You can coerce that text to a real number:
=IF(LEFT($E2,3)="CAD",--RIGHT($E2,LEN($E2)-3),0)

(the first - changes it to a negative number (but a number!). The second -
changes it back to positive.

Then you could give it the numberformat you like.

Or you could still return text, but make it look pretty:
=IF(LEFT($E2,3)="CAD",Text(RIGHT($E2,LEN($E2)-3),"000.000"),0)