Thanks Bob

That worked. Only a small niggle.

The original numbers vary in their position of hyphen

400-3000
20-300
3000-59
1000-529

Etc.

So how do I ensure hyphen gets placed at same location when
reformatted? Is there a way?

Thanks a lot for the help all you are giving.




Bob Phillips wrote:
The problem is that 162-15 is not a number but text, and formatting doesn't
work on text.

Either enter the number without the - and use a custom format of
"[$-2000000]#0-00", or use the VBA to strip it out and format it

With Sheet1.Range("B3")
.Value = Replace(.Value, "-", "")
.NumberFormat = "[$-2000000]#0-00"
End With


--
---
HTH

Bob