Finding and moving hypens from end to beginning of cell

  • Thread starter Thread starter Amy
  • Start date Start date
A

Amy

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 cells>format
menu.

Hope it helps
 
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.
 
Back
Top