Convert Text to number

[tomdabom76]

When I import data from a particular source, negative numbers show up with a hyphen on the end (i.e. xxxx.xx-). When I try and convert this to a number using the value function, Excel gives an error. Are there a combination of formulas that will be smart enough to know to put the hyphen on the front so that Excel can recognize and convert it to a number no matter how many characters are in the string.

 

[timorrill]

Try this:

Assuming the value you want to convert is in cell A1:

=IF(RIGHT(A1,1)="-",-VALUE(LEFT(A1,LEN(A1)-1)),VALUE(A1))

This formula should convert the xxx.xx- to a number format.

 

[tomdabom76]

Thanks.