String manipulation function...

[Frosty]

Brain teaser... I'm drawing a blank for an elegant solution... And maybe just posting this will help me organize my thoughts....

I want to use...
"Word2 %Word1% Word3"
to covert
"__Word4__"
into
"Word2 %Word4% Word3"

and also use...
"%Word1% Word2 %Word3%"
to convert
"Word4__Word5"
into
"%Word4% Word2 %Word5%"

Basically, I'm trying to develop my own syntax for a data-driven function which will build an array of phrases, but with some static text and some dynamic text (identified by being bracketed with % signs) within the list of phrases. So that I can have...

COUNTY OF %LOS ANGELES%
convert
__ORANGE
into
COUNTRY OF %ORANGE%

As well use
%FIRST% CIRCUIT COURT, %O'AHU%
to convert
SECOND__MAUI
into
%SECOND% CIRCUIT COURT, %MAUI%

The design specs are probably more than anyone would want to read... but the functionality is fairly straight forward. I could certainly change my syntax to anything, since I'm developing it on the fly... but it would be a way of flexibly altering a data table which, as long as the whatever syntax rules were followed, wouldn't require code changing...

Thoughts?

[Tinbendr]

Maybe autotext, or autocorrect.

See Greg's thought's on the matter.

[Tommy]

Frosty,

I think I would look into reg expressions. When you add MS Scripting it adds some overhead but I have found that it is very powerful and fast. There is also a set of submatches that you can use.

[Frosty]

Thanks, Tinbendr. I'm not looking for an alternate method to achieving that goal, but rather a way to use a string function to do exactly what I describe, using the first two parameters to return the third parameter.

Something along the lines of...
[vba]
Function sTester(ByVal sCurVal As String, ByVal sPrevValue) As String
Dim i As Integer
Dim arKeys() As String
Dim sReplaceText As String

arKeys = Split(sPrevValue, "%")
'the 3 item array is the simple one
If UBound(arKeys) = 2 Then
Do Until InStr(sCurVal, "__") = 0
sReplaceText = arKeys(i)
Select Case i
Case 0
sReplaceText = sReplaceText & "%"
Case 2
sReplaceText = "%" & sReplaceText
End Select
sCurVal = Replace(sCurVal, "__", sReplaceText, , 1)
i = i + 2
Loop

'this probably needs to be made more robust
Else
sCurVal = "%" & sCurVal & "%"
sReplaceText = arKeys(2)
sReplaceText = "%" & sReplaceText & "%"
sCurVal = Replace(sCurVal, "__", sReplaceText, , 1)
End If

sTester = sCurVal
End Function
[/vba] Tommy: thanks for the out-of-the-box idea. I'll look into it. The above code is neither pretty nor, I suspect, will it be as flexible as I'd like it to be when I inevitably come along and want to add more criteria.

I was originally thinking something along the lines of using multiple split arrays in some slick way, but I kept getting stuck.

Will post back if I work out some sort of better alternative to the above using regular expressions.

[Paul_Hossler]

This might not as flexible as you're looking for, but I use this in Excel to customize strings

May give you some ideas

[vba]
Option Explicit
'replaces sub-strings in StrIn in the form $1$, $2$, $10$, etc with
'that entry in Replacements
'Example:

Sub demo()
Dim s As String

s = "aaaaa $1$ ---bbbb, $2$ bbbbb, ccc = $5$-----, zzzz$4$xxxxx"
MsgBox Str_Subst(s, "1111", "2222", "3333", "4444", "5555")

s = "$1$ WORD2 $2$"
MsgBox Str_Subst(s, "WORD1", "WORD3")

s = "$1$ $2$ WORD2 $2$"
MsgBox Str_Subst(s, "WORD7", "WORD8")

End Sub

Function Str_Subst(StrIn As String, ParamArray Replacements() As Variant) As String
Dim i As Integer
Dim s As String

s = StrIn
For i = LBound(Replacements) To UBound(Replacements)
'ParamArray ALWAYS starts at entry 0, so we want $1 to be replaced by Replacements(0)
s = Application.WorksheetFunction.Substitute(s, "$" & Format(i + 1) & "$", Trim(Replacements(i)))
Next i

Str_Subst = s

End Function
[/vba]

Paul

[Tommy]

Frosty,

I didn't mean to leave you hanging but here is some reg expressions that work with your data.

Not: This does replace the %.

[vba]
Sub TestMe()
ReplaceMyString "Word2 %Word1% Word3", "%Word4%"
ReplaceMyString "%Word1% Word2 %Word3%", "%Word4%", "%Word5%"
End Sub
Function ReplaceMyString(Orig As String, Data1 As String, Optional Data2 As String) As String
Dim mRegExp As New RegExp
Dim mMatches As MatchCollection
With mRegExp
.Pattern = "[%{2,5}]"
.Global = True
.IgnoreCase = True
Set mMatches = .Execute(Orig)
If mMatches.Count > 0 Then
.Pattern = "%[A-z]*[0-9]%"
Set mMatches = .Execute(Orig)
If mMatches.Count = 1 Then
Orig = .Replace(Orig, Data1)
Else
Orig = Replace(Orig, mMatches(0), Data1)
Orig = Replace(Orig, mMatches(1), Data2)
End If
End If
End With
ReplaceMyString = Orig
End Function
[/vba]

[Frosty]

Thank you for your responses. Will post back when I have a chance to review...

Thanks again!