Previous Topic Next Topic

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

Answer 1 / 2
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
abhi
Mar 25, 2012 at 04:17 AM
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??
Abhi
Mar 28, 2012 at 03:09 PM
sorry for the late reply Venkat...
but the above code is giving me compilation error
Answer 2 / 2
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