Find & replace string in text

[BeSmart]

Hi
I want to create a worksheet_change event that searchs for a string within
text (as it's typed into a cell or after a spacebar or return is entered),
and replaces the specific string found with a different character or symbol -
without affecting the other text in the cell.

e.g. In any cell on a worksheet, a user types "X-ray and Zebra", I need
excel to find all the "x"s and replace them with "[" plus find and replace
any "z"s with "]".

Not sure if this possible? If anyone can help me to create this I'd really
appreciate it.

P.S. I'm a novice at VBA, and I'm trying to teach myself how to use VBA by
dissecting examples I find and learn about on the web, so sorry if I haven't
provided good information.....

 

[Gary''s Student]

This is coded for cell A1:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1")) Is Nothing Then
Exit Sub
End If
Set r = Target
Dim s As String
s = r.Value
s = Replace(s, "x", "[")
s = Replace(s, "X", "[")
s = Replace(s, "z", "]")
s = Replace(s, "Z", "]")
Application.EnableEvents = False
r.Value = s
Application.EnableEvents = True
End Sub

 

[BeSmart]

Thanks Gary - That worked brilliantly
One more question please...
If I want to replace the normal letter with a symbol (e.g. character code
030C which is a combining diacritical mark under the tahoma font) what do I
need to change in the code?
--
Thank for your help
BeSmart

This is coded for cell A1:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1")) Is Nothing Then
Exit Sub
End If
Set r = Target
Dim s As String
s = r.Value
s = Replace(s, "x", "[")
s = Replace(s, "X", "[")
s = Replace(s, "z", "]")
s = Replace(s, "Z", "]")
Application.EnableEvents = False
r.Value = s
Application.EnableEvents = True
End Sub

--
Gary''s Student - gsnu200738

Hi
I want to create a worksheet_change event that searchs for a string within
text (as it's typed into a cell or after a spacebar or return is entered),
and replaces the specific string found with a different character or symbol -
without affecting the other text in the cell.

e.g. In any cell on a worksheet, a user types "X-ray and Zebra", I need
excel to find all the "x"s and replace them with "[" plus find and replace
any "z"s with "]".

Not sure if this possible? If anyone can help me to create this I'd really
appreciate it.

P.S. I'm a novice at VBA, and I'm trying to teach myself how to use VBA by
dissecting examples I find and learn about on the web, so sorry if I haven't
provided good information.....

 

[Gary''s Student]

Not sure
--
Gary''s Student - gsnu200738

Thanks Gary - That worked brilliantly
One more question please...
If I want to replace the normal letter with a symbol (e.g. character code
030C which is a combining diacritical mark under the tahoma font) what do I
need to change in the code?
--
Thank for your help
BeSmart

This is coded for cell A1:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1")) Is Nothing Then
Exit Sub
End If
Set r = Target
Dim s As String
s = r.Value
s = Replace(s, "x", "[")
s = Replace(s, "X", "[")
s = Replace(s, "z", "]")
s = Replace(s, "Z", "]")
Application.EnableEvents = False
r.Value = s
Application.EnableEvents = True
End Sub

--
Gary''s Student - gsnu200738

Hi
I want to create a worksheet_change event that searchs for a string within
text (as it's typed into a cell or after a spacebar or return is entered),
and replaces the specific string found with a different character or symbol -
without affecting the other text in the cell.

e.g. In any cell on a worksheet, a user types "X-ray and Zebra", I need
excel to find all the "x"s and replace them with "[" plus find and replace
any "z"s with "]".

Not sure if this possible? If anyone can help me to create this I'd really
appreciate it.

P.S. I'm a novice at VBA, and I'm trying to teach myself how to use VBA by
dissecting examples I find and learn about on the web, so sorry if I haven't
provided good information.....

 

[Zone]

I think this gives the character you want. If the whole cell is formatted
for Tahoma, changing Gary"s line like this should replace x with it.

s = Replace(s, "x", ChrW(780))

It is a weird little character, though. It doesn't seem to want to appear
unless the character to the left of it works with this kind of character.
HTH, James

 

[BeSmart]

Thanks James - That worked perfectly.
FYI - I'm trying to type a database of pinyin (a version of chinese/english)
which needs these tones ontop of vowels, but I want to apply the tones to a
modern looking font.
One last question - The character code you used "ChrW(780)" is different to
the one I found under insert / symbols "030C" - where do I find the correct
character code numbers?

 

[BeSmart]

Don't worry - I worked through the character codes near 780 until I found the
ones I want to use.

I now have another problem - I want to apply the same function to a user
form textbox - but it's clashing because originally we created a
worksheet_change which doesn't work during the initiation and reporting from
a userform.

Can any help me to adapt the following code to apply to the current active
cell or to apply when the userform textbox is being filled in?

Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Target
Dim s As String
s = r.Value
s = Replace(s, "[", ChrW(780))
s = Replace(s, "]", ChrW(768))
s = Replace(s, "<", ChrW(772))
s = Replace(s, ">", ChrW(769))
s = Replace(s, "~", ChrW(776))
Application.EnableEvents = False
r.Value = s
Application.EnableEvents = True
End Sub

 

[Zone]

Be, The following code will work with the active cell. You will need to put
it in a standard module (not the worksheet module). To create a standard
module, with the spreadsheet on the screen, press Alt-F11 to go to the code
editor. Click Insert from the menubar, then click on Module. Copy this
code and put it in there. I would assign a shortcut key to it. To do this,
press Alt-F11 to return to the spreadsheet. Click Tools on the menubar,
then Macro, then Macros. Select PutChr from the list and click Options.
Type a letter into the little box that says Shortcut Key. z is a good key
to use. Click OK, then click Cancel. Save the file. Now you can use the
shortcut key, like Crl-z to call the subroutine. The userform would be more
complicated, because I don't know how familiar you are with userforms. If
you still want the userform code, post back. James

Sub PutChr()
Dim r As Range
Set r = ActiveCell
Dim s As String
s = r.Value
s = Replace(s, "[", ChrW(780))
s = Replace(s, "]", ChrW(768))
s = Replace(s, "<", ChrW(772))
s = Replace(s, ">", ChrW(769))
s = Replace(s, "~", ChrW(776))
r.Value = s
End Sub

Don't worry - I worked through the character codes near 780 until I found
the
ones I want to use.

I now have another problem - I want to apply the same function to a user
form textbox - but it's clashing because originally we created a
worksheet_change which doesn't work during the initiation and reporting
from
a userform.

Can any help me to adapt the following code to apply to the current active
cell or to apply when the userform textbox is being filled in?

Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Target
Dim s As String
s = r.Value
s = Replace(s, "[", ChrW(780))
s = Replace(s, "]", ChrW(768))
s = Replace(s, "<", ChrW(772))
s = Replace(s, ">", ChrW(769))
s = Replace(s, "~", ChrW(776))
Application.EnableEvents = False
r.Value = s
Application.EnableEvents = True
End Sub

--
Thank for your help
BeSmart

Thanks James - That worked perfectly.
FYI - I'm trying to type a database of pinyin (a version of
chinese/english)
which needs these tones ontop of vowels, but I want to apply the tones to
a
modern looking font.
One last question - The character code you used "ChrW(780)" is different
to
the one I found under insert / symbols "030C" - where do I find the
correct
character code numbers?

 

[BeSmart]

Thanks James

I actually included the same coding at the end of the "click" code that
activates the userform- i.e. once the data appears on my worksheet it
converts the characters into symbols, but I would still like the user to be
able to see the symbols in the userform text box as they type if possible.

In terms of my experience with userforms - I'm teaching myself so it's
minimal, but I'm assuming I double click on the textbox field on the userform
(to go into it's code) and enter change code in there - but I'm not sure what
code I write to convert the character as it's typed in the field.

--
Thank heaps for your help
BeSmart

Be, The following code will work with the active cell. You will need to put
it in a standard module (not the worksheet module). To create a standard
module, with the spreadsheet on the screen, press Alt-F11 to go to the code
editor. Click Insert from the menubar, then click on Module. Copy this
code and put it in there. I would assign a shortcut key to it. To do this,
press Alt-F11 to return to the spreadsheet. Click Tools on the menubar,
then Macro, then Macros. Select PutChr from the list and click Options.
Type a letter into the little box that says Shortcut Key. z is a good key
to use. Click OK, then click Cancel. Save the file. Now you can use the
shortcut key, like Crl-z to call the subroutine. The userform would be more
complicated, because I don't know how familiar you are with userforms. If
you still want the userform code, post back. James

Sub PutChr()
Dim r As Range
Set r = ActiveCell
Dim s As String
s = r.Value
s = Replace(s, "[", ChrW(780))
s = Replace(s, "]", ChrW(768))
s = Replace(s, "<", ChrW(772))
s = Replace(s, ">", ChrW(769))
s = Replace(s, "~", ChrW(776))
r.Value = s
End Sub

Don't worry - I worked through the character codes near 780 until I found
the
ones I want to use.

I now have another problem - I want to apply the same function to a user
form textbox - but it's clashing because originally we created a
worksheet_change which doesn't work during the initiation and reporting
from
a userform.

Can any help me to adapt the following code to apply to the current active
cell or to apply when the userform textbox is being filled in?

Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Target
Dim s As String
s = r.Value
s = Replace(s, "[", ChrW(780))
s = Replace(s, "]", ChrW(768))
s = Replace(s, "<", ChrW(772))
s = Replace(s, ">", ChrW(769))
s = Replace(s, "~", ChrW(776))
Application.EnableEvents = False
r.Value = s
Application.EnableEvents = True
End Sub

--
Thank for your help
BeSmart

Thanks James - That worked perfectly.
FYI - I'm trying to type a database of pinyin (a version of
chinese/english)
which needs these tones ontop of vowels, but I want to apply the tones to
a
modern looking font.
One last question - The character code you used "ChrW(780)" is different
to
the one I found under insert / symbols "030C" - where do I find the
correct
character code numbers?
--
Thank for your help
BeSmart


:

I think this gives the character you want. If the whole cell is
formatted
for Tahoma, changing Gary"s line like this should replace x with it.

s = Replace(s, "x", ChrW(780))

It is a weird little character, though. It doesn't seem to want to
appear
unless the character to the left of it works with this kind of
character.
HTH, James

Thanks Gary - That worked brilliantly
One more question please...
If I want to replace the normal letter with a symbol (e.g. character
code
030C which is a combining diacritical mark under the tahoma font)
what do
I
need to change in the code?

 

[Zone]

Be, I'll see what I can do with the userform and post back. In the
meantime, this will show you the ChrW characters. Open a new workbook.
In cell U1, enter
Font
In cell U2, enter
Tahoma
In cell U3, enter
Lead character
In cell U4, enter
b
Insert a standard module, copy the code below and put it in there.

Sub ShowChrs()
Dim k As Long, j As Long, c As String
Columns("a:t").ClearContents
c = [u4]
Cells.Font.Size = 13
For j = 1 To 21 Step 2
Columns(j).Font.Name = "Arial"
Columns(j + 1).Font.Name = [u2]
Next j
For j = 0 To 9
For k = 1 To 200
Cells(k, 1 + (j * 2)) = k + (j * 200)
Cells(k, 2 + (j * 2)) = c & ChrW(k + (j * 200))
Next k
Next j
Columns.ColumnWidth = 6
[a1].Select
End Sub

Run the routine to see the characters. I included a "lead character" with
each ChrW character because some of the ChrW characters won't show unless
there's a character before them. To get rid of the lead character, make
cell U4 empty. You can get a different font by putting a different font
name in cell U2. Make sure you spell it right or you'll get weird results.
James

 

[Zone]

Be, the textbox was easier than I thought. Copy this code and paste in the
userform module. It assumes your textbox is named TextBox1. If that's not
right, change all the TextBox1 to the name of your textbox. Also, be sure
to read my previous post! James

Private Sub TextBox1_Change()
Dim s As String
s = Me.TextBox1
s = Replace(s, "[", ChrW(780))
s = Replace(s, "]", ChrW(768))
s = Replace(s, "<", ChrW(772))
s = Replace(s, ">", ChrW(769))
s = Replace(s, "~", ChrW(776))
Me.TextBox1 = s
End Sub

Be, I'll see what I can do with the userform and post back. In the
meantime, this will show you the ChrW characters. Open a new workbook.
In cell U1, enter
Font
In cell U2, enter
Tahoma
In cell U3, enter
Lead character
In cell U4, enter
b
Insert a standard module, copy the code below and put it in there.

Sub ShowChrs()
Dim k As Long, j As Long, c As String
Columns("a:t").ClearContents
c = [u4]
Cells.Font.Size = 13
For j = 1 To 21 Step 2
Columns(j).Font.Name = "Arial"
Columns(j + 1).Font.Name = [u2]
Next j
For j = 0 To 9
For k = 1 To 200
Cells(k, 1 + (j * 2)) = k + (j * 200)
Cells(k, 2 + (j * 2)) = c & ChrW(k + (j * 200))
Next k
Next j
Columns.ColumnWidth = 6
[a1].Select
End Sub

Run the routine to see the characters. I included a "lead character" with
each ChrW character because some of the ChrW characters won't show unless
there's a character before them. To get rid of the lead character, make
cell U4 empty. You can get a different font by putting a different font
name in cell U2. Make sure you spell it right or you'll get weird
results.
James

Thanks James

I actually included the same coding at the end of the "click" code that
activates the userform- i.e. once the data appears on my worksheet it
converts the characters into symbols, but I would still like the user to
be
able to see the symbols in the userform text box as they type if
possible.

In terms of my experience with userforms - I'm teaching myself so it's
minimal, but I'm assuming I double click on the textbox field on the
userform
(to go into it's code) and enter change code in there - but I'm not sure
what
code I write to convert the character as it's typed in the field.

 

[BeSmart]

Hi James
Wow - both your postings worked beautifully and are extremely helpful for me
- your help is very much appreciated. I'll now go and study your code to
understand it better and learn from your smart work.
--
Thank for all your help
BeSmart

Be, the textbox was easier than I thought. Copy this code and paste in the
userform module. It assumes your textbox is named TextBox1. If that's not
right, change all the TextBox1 to the name of your textbox. Also, be sure
to read my previous post! James

Private Sub TextBox1_Change()
Dim s As String
s = Me.TextBox1
s = Replace(s, "[", ChrW(780))
s = Replace(s, "]", ChrW(768))
s = Replace(s, "<", ChrW(772))
s = Replace(s, ">", ChrW(769))
s = Replace(s, "~", ChrW(776))
Me.TextBox1 = s
End Sub

Be, I'll see what I can do with the userform and post back. In the
meantime, this will show you the ChrW characters. Open a new workbook.
In cell U1, enter
Font
In cell U2, enter
Tahoma
In cell U3, enter
Lead character
In cell U4, enter
b
Insert a standard module, copy the code below and put it in there.

Sub ShowChrs()
Dim k As Long, j As Long, c As String
Columns("a:t").ClearContents
c = [u4]
Cells.Font.Size = 13
For j = 1 To 21 Step 2
Columns(j).Font.Name = "Arial"
Columns(j + 1).Font.Name = [u2]
Next j
For j = 0 To 9
For k = 1 To 200
Cells(k, 1 + (j * 2)) = k + (j * 200)
Cells(k, 2 + (j * 2)) = c & ChrW(k + (j * 200))
Next k
Next j
Columns.ColumnWidth = 6
[a1].Select
End Sub

Run the routine to see the characters. I included a "lead character" with
each ChrW character because some of the ChrW characters won't show unless
there's a character before them. To get rid of the lead character, make
cell U4 empty. You can get a different font by putting a different font
name in cell U2. Make sure you spell it right or you'll get weird
results.
James

Thanks James

I actually included the same coding at the end of the "click" code that
activates the userform- i.e. once the data appears on my worksheet it
converts the characters into symbols, but I would still like the user to
be
able to see the symbols in the userform text box as they type if
possible.

In terms of my experience with userforms - I'm teaching myself so it's
minimal, but I'm assuming I double click on the textbox field on the
userform
(to go into it's code) and enter change code in there - but I'm not sure
what
code I write to convert the character as it's typed in the field.

 

[Zone]

Be, I'm glad to help. Thanks for the feedback! James

Hi James
Wow - both your postings worked beautifully and are extremely helpful for
me
- your help is very much appreciated. I'll now go and study your code to
understand it better and learn from your smart work.
--
Thank for all your help
BeSmart

Be, the textbox was easier than I thought. Copy this code and paste in
the
userform module. It assumes your textbox is named TextBox1. If that's
not
right, change all the TextBox1 to the name of your textbox. Also, be
sure
to read my previous post! James

Private Sub TextBox1_Change()
Dim s As String
s = Me.TextBox1
s = Replace(s, "[", ChrW(780))
s = Replace(s, "]", ChrW(768))
s = Replace(s, "<", ChrW(772))
s = Replace(s, ">", ChrW(769))
s = Replace(s, "~", ChrW(776))
Me.TextBox1 = s
End Sub

Be, I'll see what I can do with the userform and post back. In the
meantime, this will show you the ChrW characters. Open a new workbook.
In cell U1, enter
Font
In cell U2, enter
Tahoma
In cell U3, enter
Lead character
In cell U4, enter
b
Insert a standard module, copy the code below and put it in there.

Sub ShowChrs()
Dim k As Long, j As Long, c As String
Columns("a:t").ClearContents
c = [u4]
Cells.Font.Size = 13
For j = 1 To 21 Step 2
Columns(j).Font.Name = "Arial"
Columns(j + 1).Font.Name = [u2]
Next j
For j = 0 To 9
For k = 1 To 200
Cells(k, 1 + (j * 2)) = k + (j * 200)
Cells(k, 2 + (j * 2)) = c & ChrW(k + (j * 200))
Next k
Next j
Columns.ColumnWidth = 6
[a1].Select
End Sub

Run the routine to see the characters. I included a "lead character"
with
each ChrW character because some of the ChrW characters won't show
unless
there's a character before them. To get rid of the lead character,
make
cell U4 empty. You can get a different font by putting a different
font
name in cell U2. Make sure you spell it right or you'll get weird
results.
James

Thanks James

I actually included the same coding at the end of the "click" code
that
activates the userform- i.e. once the data appears on my worksheet it
converts the characters into symbols, but I would still like the user
to
be
able to see the symbols in the userform text box as they type if
possible.

In terms of my experience with userforms - I'm teaching myself so it's
minimal, but I'm assuming I double click on the textbox field on the
userform
(to go into it's code) and enter change code in there - but I'm not
sure
what
code I write to convert the character as it's typed in the field.