Mass Find / Replace with Formula - Possible?

S

SteveO59L

Well-known Member

Joined

Apr 21, 2004

Messages

7,899

• Jun 17, 2010

• #2

Have a look at vlookup()

 

Upvote 0

S

Southerncentralrain

Board Regular

Joined

Jul 6, 2008

Messages

115

• Jun 17, 2010

• #3

The words that are being replaced are not the only words in the cell so I don't know how to use vlookup for a partial replacement.

Here is an example:

Old Word= Hendrix Clay
New Word = 45446-04

Original Phrase = ST-NW-18-D-Hendrix Clay
Corrected Phrase = ST-NW-18-D-45446-04

Does that make sense?

 

Upvote 0

S

SteveO59L

Well-known Member

Joined

Apr 21, 2004

Messages

7,899

• Jun 17, 2010

• #4

Yep, thats significantly different.

I think that a coded solution would be required. If you are ok with that.

 

Upvote 0

S

SteveO59L

Well-known Member

Joined

Apr 21, 2004

Messages

7,899

• Jun 17, 2010

• #5

Something along the lines of

Dim c1 As Range, rng1
Dim c2 As Range, rng2
Dim chk_val As String
Dim chk_val2 As String
Set rng1 = Range("a1:a13")
Set rng2 = Range("h1:h52")
For Each c1 In rng1
chk_val = c1.Value
chk_val2 = c1.Offset(0, 1).Value

For Each c2 In rng2
If InStr(c2, chk_val) Then
c2.Value = Replace(c2, chk_val, chk_val2)
End If
Next c2
Next c1

 

Upvote 0

Dannyh1

Well-known Member

Joined

Nov 18, 2009

Messages

1,144

• Jun 17, 2010

• #6

.

 

Last edited: Jun 17, 2010

Upvote 0

Dannyh1

Well-known Member

Joined

Nov 18, 2009

Messages

1,144

• Jun 17, 2010

• #7

Hi- will the 1st part always be same length?

ST-NW-18-D-

i.e 11

 

Upvote 0

S

Southerncentralrain

Board Regular

Joined

Jul 6, 2008

Messages

115

• Jun 17, 2010

• #8

No, they vary in length.

 

Upvote 0