Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming,microsoft.public.word.vba.beginners,microsoft.public.word.vba.general
external usenet poster
 
Posts: 5
Default Regular expressions for replacements in Excel?

Well, below is the current version of the code, but unless someone is
willing to help and can clarify how to make the MultiLine thing work, I'm
probably going to let it stand. This is about 5 times faster than the
original version, but basically it's just inlining the subroutine call and
reducing the overhead for object creation. This will process the 2000 lines
in around 4 seconds.

By using a "Dim theText() as String" statement the system seemed to accept
the MultiLine assignment, but in reality only the first string from Cells
was copied into my array, and all of my experiments failed. I wasn't able to
find any directly applicable source code examples, and I couldn't twist any
of the examples I did find into working as expected... My current conclusion
is that there are very few people using the MultiLine capability, and none
of them are monitoring any of these newsgroups.

The other approach of using large strings seems implausible since there's a
32 KB size limit on the strings, and the total file is something over 200
KB. However, I may still be able to pursue that approach if I can devise
some criteria for lumping things into reasonably small packages...

Basically you can take the following as an example of how to use true
regular expressions within Excel via a VB macro:

Sub FastClean()

Dim startTime
startTime = Now

Dim I As Long
Dim lastI As Long
Dim S As String

Set aRegExp = CreateObject("vbscript.regexp")
With aRegExp
.Global = True
.IgnoreCase = True
' .MultiLine = True
End With

ActiveCell.SpecialCells(xlLastCell).Select
lastI = ActiveCell.Row

For I = 1 To lastI
S = Cells(I, 1)

'kill first three parameters
aRegExp.Pattern = "(height|width|border)=""?\d+%?""? ?"
S = aRegExp.Replace(S, "")

' Eight more patterns and replacements

Cells(I, 1) = S
Next

MsgBox "Finished in " + Format(Now - startTime, "ss.s") + " seconds."
End Sub

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Get rid of with regular expressions Howdy Excel Discussion (Misc queries) 1 January 18th 10 07:42 PM
Regular expressions in VB FiluDlidu Excel Discussion (Misc queries) 4 March 21st 08 01:10 AM
Substring in excel? How about regular expressions? Samuel Excel Discussion (Misc queries) 8 May 22nd 06 04:43 PM
Regular expressions in Excel vigi98 Excel Discussion (Misc queries) 3 November 10th 05 04:40 PM
Regular Expressions in VBA & Excel including an interactive tool Tushar Mehta Excel Programming 0 December 30th 03 03:17 AM


All times are GMT +1. The time now is 01:23 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"