Hi,

I want to be able to find and change all ###- entries
into -### in a worksheet (or a selected range). i.e. Move
the hyphen from the end of the cell to the beginning
(negative number) and remove any spaces that may be at the
beginning or end of the cell.

Thanks for any help.

Example
123-(space) change to -123
(space)234- -234
12-%(space) -12%
(space)-(space) leave as is or remove spaces

Amy,

A find & replace operation on the affected cells needs to be done to
find all spaces and replace them with nothing. (that means do not type
anything in the replace with box).

Most of the cells should now be numeric and excell will recognise them
as such.

If any cells are still being treated as text, then in an empty part of
your worksheet enter a 1 ( the number that is).
Copy that cell. (with the 1 in it)
Highlight all of your data cells and use paste special multiply .
This will cause excel to treat all of the cells as numbers.

Fianlly to move the minus sign.
That is dealt with in online help concerning number formats, of you
could use the pre-worked formats supplied with excel.
Either way, the area you need to work with is in the cellsformat
menu.

Hope it helps


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements

Sub ConvertMinus()
Dim sStr As String
For Each cell In Selection.SpecialCells(xlConstants, xlTextValues)
sStr = Application.Substitute(Trim(cell), " ", "")
If InStr(sStr, "-") Then
sStr = Application.Substitute(sStr, "-", "")
If Len(sStr) < 0 Then
If InStr(sStr, "%") Then
sStr = Application.Substitute(sStr, "%", "")
If IsNumeric(Left(sStr, Len(sStr) - 1)) Then
cell.Value = CDbl(sStr) / 100
cell.NumberFormat = "#%"
End If
Else
If IsNumeric(Left(sStr, Len(sStr) - 1)) Then
cell.Value = CDbl(sStr)
End If
End If
End If
End If
Next

End Sub


Worked with your examples.

--
Regards,
Tom Ogilvy



--
Regards,
Tom Ogilvy


"Amy" wrote in message
...
Hi,

I want to be able to find and change all ###- entries
into -### in a worksheet (or a selected range). i.e. Move
the hyphen from the end of the cell to the beginning
(negative number) and remove any spaces that may be at the
beginning or end of the cell.

Thanks for any help.

Example
123-(space) change to -123
(space)234- -234
12-%(space) -12%
(space)-(space) leave as is or remove spaces