Menu
When I input Euro or Sterling values into an excel sheet, they are automatically recognized as numbers/currency and right aligned and I can also extract their value.
e.g. £10 is automatically right aligned. Using the formula =VALUE(A1) returns 10 as it should. This also worked when using the € symbol. However if I used the $ symbol, the value is recognized as text and using the above formula results in #VALUE!. Maybe it has something to do with my Windows regional/language settings which are for UK not US.
EDIT: I have two entries under accounting formats: £ English (U.K.) $ English (U.S.)
757
On the Tools menu, click Options. In the Options dialog box, click the Error Checking tab. In the Settings section, click to select the Enable background error checking check box. In the Rules section, make sure the Number stored as text rule is selected, and then click OK.
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.
You can speed up entering the dollar signs by using the function key F4 when editing the formula, if the cursor is on a cell reference in the formula, repeatedly hitting the F4 key, toggles between no dollar signs, both dollar signs, just the row and just the column.
Convert Text to Numbers Using 'Convert to Number' OptionSelect all the cells that you want to convert from text to numbers. Click on the yellow diamond shape icon that appears at the top right. From the menu that appears, select 'Convert to Number' option.
Alternatively to @Matt 's answer, if your currency sign is not the first character or has a random position in the text, you can use =SUBSTITUTE(A1,"$","").
126
I figured out a work around for anyone with a similar problem. I used =VALUE(REPLACE(A1,1,1,"")) in order to remove the dollar sign and just get the value of the number, the number can then be formatted using the '$ English (U.S.)' accounting format.
26
$ is used for fixed cell references - e.g. $A$10. This is very useful when copying a formula or filling an entire column from it. You should just format your cell as currency and set the formatting to display a dollar sign instead.
3
What worked for me was to use the replace command. Highlight the cells, click on edit, then replace, type in $ and replace with +
2
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
