Find and replace a word in a string

[Closed]
Report
-
 Abhi -
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.

2 replies

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
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
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
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