Hi Louis
Try this :-
Function FixTooManySingleQuotes(ByRef sTextToFix As String) As String
Const SINGLEQUOTE = "'"
Const TWOQUOTES = "''"
'
While InStr(sTextToFix, TWOQUOTES) > 0
sTextToFix = Replace(sTextToFix, TWOQUOTES, SINGLEQUOTE)
Wend
FixTooManySingleQuotes = sTextToFix
End Function
You might want to flag odd numbers of quotes also.
VB does not have Regular Expressions built in. You have to make a reference to the MicroSoft VBScript Regular Expressions dll. If all you want to do is fix double quotes then RegEx is a giant overkill.
Regards
Derek +++
>________________________________
> From: "1z@compuserve.com" <1z@compuserve.com>
>To: ExcelVBA@yahoogroups.com
>Sent: Monday, 19 March 2012, 15:14
>Subject: Re: [ExcelVBA] Re: Help Needed in Regular Expression Pattern
>
>
>
>
>Is this from a different cell? I'm curious as to how you've managed to get so many single quotes in a string?
>
>You could write a function for it. Is there a max number of quotes? Are there equal numbers of quotes on either side of the text?
>
>... And you can always send the file or an example to a moderator to check and upload to the Files/Files Needing Help section.
>
>Lisa
>
>Sent: Mon, Mar 19, 2012 3:56 pm
>Subject: [ExcelVBA] Re: Help Needed in Regular Expression Pattern
>
>Thanks - I'm trying to replace any group of multiple quotes, as in your example
>ith any single character to replace the group.
>o, like this:
>'''''hello'''''
>hello$
>--- In ExcelVBA@yahoogroups.com, Gary Morris <gwmorris@...> wrote:
>
>I can't determine what it is you are after. You say that you are
>trying to replace"all multiple occurrences of single quotes", so
>does that mean like a cell entry like: '''''''too many quotes'''''''?
>And then, what are you replacing the group of quotes with?
>Double quotes? Are you wanting to consolidate them to just
>one quote, as in the above: 'too many quotes' would then be
>the result? Without enough information, not many people can
>or will even try to help. Maybe you could post an example
>with before and after or something...
>
>
>G.
>
>
>[Non-text portions of this message have been removed]
>
>
>
>
>
[Non-text portions of this message have been removed]
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar