Find and replace error
 

I have a worksheet that was created by pasting in data from a word table. I
am in the process of cleaning up the data which will in turn be used to
populate a database. As part of my clean up I am using the find and replace
function to take out commas. Some of the text is lengthy. When I get to a
lengthy cell, Excel is giving me an error stating that the formula is too
long.
Thoughts? Work arounds?

Thanks,
Michelle

Do you still have the table in word?

If yes, clean it up there.

If no, you can cycle through each cell and remove the commas.

Option Explicit
Sub testme()
Dim myCell As Range
Dim rng As Range

Set rng = Selection

For Each myCell In rng.Cells
myCell.Value = Application.Substitute(myCell.Value, ",", " ")
Next myCell

End Sub

Select your cells and run this macro.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

meesh1224 wrote:

I have a worksheet that was created by pasting in data from a word table. I
am in the process of cleaning up the data which will in turn be used to
populate a database. As part of my clean up I am using the find and replace
function to take out commas. Some of the text is lengthy. When I get to a
lengthy cell, Excel is giving me an error stating that the formula is too
long.
Thoughts? Work arounds?

Thanks,
Michelle

--

Dave Peterson