Need to change a character

[rwmill9716]

I have copied a table from an online service. It's displayed negative numbers using a different symbol (instead of a conventional minus). For example, the -$432 is not treated as a negative number when I try to add it to $1,467.97 in the first column; hence, the #Value! error. How do I change this to a true negative $432? Note, I formatted the last column as currency.

 

[Joe4]

It looks like it is entered as "Text" (or is being treated as "Text").
You can convert it to a valid number by doing a "Text to Columns" on that column.
Just select that column, go to the Data menu, select "Text to Columns", and click Finish.

This should hopefully convert all those text entries to valid numbers so your mathematical functions will work!

 

[Fluff]

That looks as though it's either an en dash or an em dash. Try

=SUBSTITUTE(F2,CHAR(150),"-")+A2

 

[rwmill9716]

Thanks, Fluff, your solution worked fine.

Joe4, I tried changing the column as you suggested but that didn't work for some reason

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Joe4]

Joe4, I tried changing the column as you suggested but that didn't work for some reason

As Fluff suggested, maybe it wasn't the normal dash, so that method won't convert it to a number.
That sometimes happen on data that comes from the Web or some other application. You can get all sorts of weird characters.

Glad Fluff's solution works for you.