Finding and moving hypens from end to beginning of cell
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
|