Find and replace a word in a string

Closed
abhi - Mar 22, 2012 at 03:21 PM
 Abhi - Mar 28, 2012 at 03:09 PM
Hello,


I am looking for code where I can find and replace a word in a given connection string. e.g.
cell A1 contains
"int_dog int (10)"
and i want to replace only "int" with number, but

with the code below:
Cells.Replace What:="int", Replacement:="number", LookAt:=xlValue, _
SearchOrder:=xlByRows, MatchCase:=False

i get output as below

number_dog number (10)"

Issue is i want to replace "int" only which is second field in the string.

Any help would be much appreciated!

Thanks in advance.
Related:

2 responses

venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Mar 22, 2012 at 10:50 PM
many ways of doing it.
following macro is little structured so that you can understand the steps

type

int_dog int (10)

in A1

then run this macro



Sub test()
Dim x As String, xx As String, j As Integer, y As String, xxx As String
x = Range("a1").Value
xx = "int"
j = Len(xx)
y = InStr(j + 1, x, xx)
'MsgBox y
xxx = Left(x, y - 1) & "number" & Right(x, Len(x) - j - y)
MsgBox xxx
End Sub
Thanks for ur response venkat !

but i guess the example explained above by me is not clear...

the scenario is like this...

I have data in range A1 to A50 which have values as below:

person_name name,
place_name name ,
animal_name name,
item_name name
.
.
. and so on.

Now the first string in A1 is as below

person_name name,

I want to replace second occurrence of "name" (which is after space) in this string with "size" and output should be as below:

person_name size,
place_name size
.
.
and so on

But what I am getting is

person_size size,
place_size size,
.
. and so on.

How can I stop the first occurrence of "name" which is with underscore from not getting replaced.

Can I use some delimiter property or make use of the underscore with the first occurrence of name??
sorry for the late reply Venkat...
but the above code is giving me compilation error
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Mar 26, 2012 at 01:23 AM
TRY THIS MACRO . the result is in column B



Sub test()
Dim r As Range, c As Range, j As Long, x As String, xx As String
Set r = Range(Range("A2"), Range("A2").End(xlDown))
x = "name"
'DEFINITION OF x MAY BE CHANGED WHEN NECESSARY
j = Len(x)
For Each c In r
xx = Left(c.Value, Len(c.Value) - j - 1) & " " & "size"
'MsgBox xx
c.Offset(0, 1) = xx
Next c
End Sub







the data and result will be like this finally

existing string corrected string
person_name name person_name size
place_name name place_name size
animal_name name animal_name size
item_name name item_name size