Substituting substrings

[Guest]

Hi

I need a macro which will search a range of cells containing various data
(product descriptions) and replace certain characters. The extent of the
range is variable so it might be better to select the range before running
the macro.

What I need to do is search each data string and identify if it contains any
of the characters +, ^, %, ~, (, ), {, }, [, ]. Where the data does the
character has to be replaced with {+}, {^} etc wherever it occurs. The new
string, containing any substituted strings, needs to overwrite the original
data contained in the cell to maintain other cell references within the
workbook

Thanks for any help

 

[Guest]

Highlighting the cells and then rtunning this macro will work. Notice the ~~
appears twice. Not sure what needed to be replace with sttrings containing
commas.

Sub replacechars()

Selection.Replace what:="+", replacement:="{+}"
Selection.Replace what:="^", replacement:="{^}"
Selection.Replace what:="%", replacement:="{%}"
Selection.Replace what:="~~", replacement:="{~}"


End Sub

 

[Ron Rosenfeld]

Hi

I need a macro which will search a range of cells containing various data
(product descriptions) and replace certain characters. The extent of the
range is variable so it might be better to select the range before running
the macro.

What I need to do is search each data string and identify if it contains any
of the characters +, ^, %, ~, (, ), {, }, [, ]. Where the data does the
character has to be replaced with {+}, {^} etc wherever it occurs. The new
string, containing any substituted strings, needs to overwrite the original
data contained in the cell to maintain other cell references within the
workbook

Thanks for any help

This Sub will do that on Selection.

Of course, you might want to define your requirements a bit more. What do you
want to do if the string contains (+). Given your specifications, you would
want: (()(+)())

If that's the case, then OK. If not, then ??


=================================================

Sub ReplaceSpecial()
Dim c As Range
Dim oRegex As Object
Const sPattern As String = "([+^%~(){}[\]])"
Const rStr As String = "($1)"

Set oRegex = CreateObject("VBScript.RegExp")
oRegex.IgnoreCase = True
oRegex.Global = True
oRegex.MultiLine = True

oRegex.Pattern = sPattern

For Each c In Selection
With oRegex
If .Test(c.Text) = True Then
c.Value = .Replace(c.Text, rStr)
End If
End With
Next c

End Sub
==================================
--ron

 

[Guest]

Hi Ron

My specification does not cover the case you asked about. I'll have to go
back to my colleague for clarification and get beck to you.

Thanks

G

Hi

I need a macro which will search a range of cells containing various data
(product descriptions) and replace certain characters. The extent of the
range is variable so it might be better to select the range before running
the macro.

What I need to do is search each data string and identify if it contains any
of the characters +, ^, %, ~, (, ), {, }, [, ]. Where the data does the
character has to be replaced with {+}, {^} etc wherever it occurs. The new
string, containing any substituted strings, needs to overwrite the original
data contained in the cell to maintain other cell references within the
workbook

Thanks for any help

This Sub will do that on Selection.

Of course, you might want to define your requirements a bit more. What do you
want to do if the string contains (+). Given your specifications, you would
want: (()(+)())

If that's the case, then OK. If not, then ??


=================================================

Sub ReplaceSpecial()
Dim c As Range
Dim oRegex As Object
Const sPattern As String = "([+^%~(){}[\]])"
Const rStr As String = "($1)"

Set oRegex = CreateObject("VBScript.RegExp")
oRegex.IgnoreCase = True
oRegex.Global = True
oRegex.MultiLine = True

oRegex.Pattern = sPattern

For Each c In Selection
With oRegex
If .Test(c.Text) = True Then
c.Value = .Replace(c.Text, rStr)
End If
End With
Next c

End Sub
==================================
--ron