6

I have to replace one character with another in Excel file.

I have used following Replace function, but due to exceeds of 1024 character limit in some cells, it stops there.

Sub Replace()

    With Sheets("Sheet1").Range("A1:A629")   

      .Cells.Replace ",", ";", xlPart, xlByRows, False

    End With

End Sub

I got to know Substitute function would do

Cells(1, 2) = "=SUBSTITUTE(A1,"","","";"")"

But how do I use that for cell range?

ZygD
  • 22,092
  • 39
  • 79
  • 102
Shabar
  • 2,617
  • 11
  • 57
  • 98

2 Answers2

8

Try this. Note that it uses the VBA Replace function, so you need to rename your 'Replace` subroutine.

Sub ReplaceText()

    For Each c In Sheets("Sheet1").Range("A1:A629").Cells

      c = Replace(c.Value, ",", ";")

    Next c

End Sub

Note: This will only work if you have values in the cells, no Formulas. Because Excel has a formula length limit of 1024 characters. But given you have this specific error, your cells must not be formulas.

psiphi75
  • 1,985
  • 1
  • 24
  • 36
  • Thanks for your reply, It gives an `Compile error : Wrong number of arguments or invalid property assignment `. But arguments should be fined as per Function I believe. – Shabar Jul 10 '13 at 02:56
  • Strange, it ran for me on Excel 2010. Did you change the name of the sub to `ReplaceText`? What version of Excel do you have? – psiphi75 Jul 10 '13 at 03:30
  • I am using 2003. Yes used Subroutine as `ReplaceLengthyText `. Can it be version issue? – Shabar Jul 10 '13 at 03:39
  • Try the above code, it's been updated, although I see you have an answer. It should work for 2003 as well. I would argue that my method is neater, since you are using native VBA code... and my guess is that it would run faster too. – psiphi75 Jul 10 '13 at 04:08
  • Appreciated your response. But still I am getting the same compile error. It basically complains about the `Replace ` method. Still struggling ti figure it out. Did you try it in 2003 version? – Shabar Jul 10 '13 at 07:17
8

Try this 

Sub Replace()

    Dim rng As Range, cell As Range
    Set rng = Sheets("Sheet1").Range("A1:A629")

    For Each cell In rng
        cell = WorksheetFunction.Substitute(cell, ",", ";")
    Next
End Sub

enter image description here

Santosh
  • 12,175
  • 4
  • 41
  • 72