VBA replace only whole string

Question

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

Soulfire · Accepted Answer · 2015-04-30 19:36:45Z

4

Change the LookAt:=xlPart to LookAt:=xlWhole

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

answered Apr 30, 2015 at 19:36

Soulfire

4,3161 gold badge25 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Kim K. · Accepted Answer · 2022-09-09 12:19:09Z

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 ;)

Collectives™ on Stack Overflow

VBA replace only whole string

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related