Replace Words with exact match in Large Text using VBA

[mathsbeauty]

The large text is imported from clipboard. I want to have a replace function in excel vba similar to replace function in ms word with the option find "whole words only". The replace function in VBA or worksheet function substitute is of no use. Here is what I am trying to do:

Dim DataObj As MSForms.DataObject
Set DataObj = New MSForms.DataObject
DataObj.GetFromClipboard
Text = DataObj.GetText(1)
NewText = replace(Text, "101", "102")

This replaces 101 to 102 but also 1010 to 1020. I just want to replace with exact match (that is just want to replace 101 by 102 but not 1010 to 1020). How can I have excel vba function to achieve this?

 

[shg]

Assuming the only delimiter is space,

  Dim oDO           As MSForms.DataObject
  Dim s             As String

  Set oDO = New MSForms.DataObject
  oDO.SetText "101 1010 102 101"
  oDO.PutInClipboard

  oDO.GetFromClipboard
  s = oDO.GetText(1)
  s = Trim(Replace(" " & s & " ", " 101 ", " 102 "))
  Debug.Print s

 

[mathsbeauty]

It works great...Thanks

 

[shg]

You're welcome.