CODEWITHSUNDEEP
vbaexcelreplacesyntax
Tom Ruh
Tom Ruh

Reputation: 336

VBA replace only whole string

Using replace in VBA, it replaces all occurances of find. How do i limit it to only replace if find is the whole string?

Cells.Replace What:="0", Replacement:="n/a", LookAt:= _
    xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False

Replaces 12034 and 0 with 12n/a34 and n/a

Instead will ignore 12034 and only replace 0 with n/a

Upvotes: 2

Views: 2875

Answers (2)

Kim K.
Kim K.

Reputation: 121

It would be great if there was a third option: to look for any Whole word IN the entire String. Unfortunately it is either any part of the word or the entire string.

E.g. when trying to remove "in" in the string "I'm going to ride in a car" will either match nothing with xlWhole, or all with xlPart, resulting in the unwanted "I'm gog to ride a car".

This less nice solution works for us, but I'd rather use the other one, if only it had that 3rd flag.

stopwords = Array("in", "the")
text = "I'm going to ride in a car"

For Each c In stopwords    
    text = Replace(text, " " & c & " ", " ")
Next c

It's been 7 years for this thread, but maybe somebody can use this solution, or provide a better one for me ;)

Upvotes: 0

Soulfire
Soulfire

Reputation: 4296

Change the LookAt:=xlPart to LookAt:=xlWhole

xlPart will match any text within the cell, whereas xlWhole will match only the entire cell entry.

Upvotes: 4

Related Questions