Excel Find and Replace, Cell Characters exceed 1024 and cannot find and replace

EazyWorks
EazyWorks used Ask the Experts™
on
I need to scan my worksheet for a value of:
 'mystring.aspx'
and replace it with:
 'http://mywebsite.com/index.php?option=com_content&view=article&id=116'
The problem is, my cells exceed 1024 characters and I cannot use find and replace.  I know the data exists, however it does not find it for replacing.

I do have expresso for composing reg ex strings, but I am not sure how to create a replace string.  I will also need to do this for about 100 other entries.

Notes:
I will be searching column E of my excel file, sheet 'jos_content'.
My find string will be on column B of my excel file, sheet 'FandR'
My replace string will be on Column C of my excel file, sheet 'FandR'

Thanks!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Can you post an example file? Is there a problem for you if the solution is made with Perl?

Author

Commented:
I prefer to be able to run the macro/module from within excel.  However, here is the file that I am working with.
jos-content.xls
Terry WoodsIT Guru
Most Valuable Expert 2011

Commented:
This might help you?:
http://www.tmehta.com/regexp/
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Commented:
In this file is nothing in column B and C in FandR sheet,only in A.
The find and replace strings are only in B1 and C1, or there are different strings from B1/C1 till B416/C416.

Author

Commented:
sorry, they are not yet populated.  But you can test it using your own string.  
ǩa̹̼͍̓̂ͪͤͭ̓u͈̳̟͕̬ͩ͂̌͌̾̀ͪf̭̤͉̅̋͛͂̓͛̈m̩̘̱̃e͙̳͊̑̂ͦ̌ͯ̚d͋̋ͧ̑ͯ͛̉Glanced up at my screen and thought I had coded the Matrix...  Turns out, I just fell asleep on the keyboard.
Most Valuable Expert 2011
Top Expert 2015

Commented:
What do you mean by

    "but I am not sure how to create a replace string."

Author

Commented:
Hi kaufmed.  Im not sure how to make a regex expression in expresso that will replace a finding.  Besides, I think there are too many to cover.

I think a macro or vba code would be best would be best.

I have tried some 'advanced find/replace' add-ins but they end up messing up the replace string.
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
The code in the snippet takes a few seconds to troll through all 5000 cells in column E, and makes changes fairly deep in the text (well beyond the 1024 character limit). I was using Excel 2003 for my test.
Sub ReplaceStr()
 Dim RepWhat As String
 Dim RepWith As String
 Dim cel As Range, rng As Range
 
 RepWhat = InputBox("Find what?")
 RepWith = InputBox("Replace with what")

 Set rng = Range("e1:e5000")
 
 Application.ScreenUpdating = False
 For Each cel In rng.Cells
    cel.Value = Replace(cel.Value, RepWhat, RepWith)
 Next
 Application.ScreenUpdating = True
End Sub

Open in new window

Author

Commented:
@byundt- this code works as I have seen it in another post.  However, once you are tied to the inputbox, you cannot switch between workbooks to copy/paste the values.  

Author

Commented:
Here is an updated file of what needs replacing.
Sheet1 has my find fields (Column A), and my replace string (Column B)

I need to search Column E of Sheet 'jos_content' to find and replace.
Test.xls
Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
I revised my macro so it would work with your lists.

Brad
Sub ReplaceStr()
Dim s As String
Dim vReplace As Variant
Dim cel As Range, rng As Range, rngReplace As Range
Dim i As Long, n As Long

With Worksheets("jos_content")
    Set rng = .Range("E1")           'First cell with data
    Set rng = Range(rng, .Cells(.Rows.Count, rng.Column).End(xlUp))
End With

With Worksheets("Sheet1")
    Set rngReplace = .Range("A1")    'First cell with strings to find
    Set rngReplace = Range(rngReplace, .Cells(.Rows.Count, rngReplace.Column).End(xlUp)).Resize(, 2)
    vReplace = rngReplace.Value
    n = rngReplace.Rows.Count
End With

Application.ScreenUpdating = False
For Each cel In rng.Cells
    s = cel.Value
    For i = 1 To n
        s = Replace(s, vReplace(i, 1), vReplace(i, 2))
    Next
    cel.Value = s
Next
Application.ScreenUpdating = True
End Sub

Open in new window

Author

Commented:
Wow... it worked so fast that I dont know if it worked.  haha.  No, I tested it and its perfect.

Thanks for all your help!
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
EazyWorks,
FWIW, I was impressed with how fast the code ran on your sample data myself.

Thanks for the kind words and grade!

Brad

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial