Excel dollar sign text not being converted to a number
What worked for me was to use the replace command. Highlight the cells, click on edit, then replace, type in $ and replace with +
$
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.
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.
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,"$","")
.