removing parentheses

J

Jay

80 spreadsheets with address' - just the numbers - 250 - 350 count in
column B. 1,2,3,4 and 5 digits. some are inside parentheses for one
reason or another. how can i remove the parens and leave the number
intact in the same cell?
 
M

Mike

try this 1 is for column A and 2 is for the startingr row. Change for your
needs.
Sub removeParentheses()
Dim rng As Range
Dim Cell As Range
Set rng = Range(Cells(2, 1), Cells(Rows.Count, 1).End(xlUp))
For Each Cell In rng
Cell.Value = Replace(Cell.Value, """", "")
Next Cell
End Sub
 
J

Jay Fincannon

that's not what i'm looking for. i don't understand
Cell.Value = Replace(Cell.Value, """", "")
 
G

Gary''s Student

How about:

Sub cleanum()
Dim rr As Range, r As Range
Set rr = ActiveSheet.Cells.SpecialCells(xlCellTypeConstants)
For Each r In rr
r.Value = Replace(r.Value, ")", "")
r.Value = Replace(r.Value, "(", "")
Next
End Sub
 
R

Rick Rothstein

It looks like you can eliminate the loop...

Sub cleanum()
Dim R As Range
Set R = ActiveSheet.Cells.SpecialCells(xlCellTypeConstants)
R.Replace ")", ""
R.Replace "(", ""
R.Value = R.Value
End Sub

The last line is needed to convert the text to actual numbers.
 
Top