[Lynchie]

Hi,

Can someone please help? I need a way to be able to find and replace in Excel on a mass scale. I have three colums A, B, C. I want to be able to find the value from Column A and replace it with the value in the same row in column B wherever it appears in Column C

Column A ********Column B ******** Column C
Cat ************Dog************* www.lynchie.com/cat/UK
etc etc**************************www.lynchie.com/horse/uk
*******************************www.lynchie.com/cat/uk
*******************************www.lynchie.com/mouse/uk

(Apologies for the bad rendering of an Excel Sheet)

This is a simple find and replace. However, I have approximately 1,000 pairs in column A and B which need to be found and replaced in Column C (and Column C is 100,000 rows+).


Is there any macro / process which I could use or I'm I doomed to do a find and replace 1,000 times?

Any help / suggestions would be hugely appreciated,

Thanks

Lynchie

[terabytecomputer]

I'm not sure if you have it worded backwards, or if I'm thinking backwards. What you say you're trying to do doesn't make sense to me.

If you have this:
Column A ********Column B ******** Column C
Cat ************Dog************* www.lynchie.com/cat/UK
Mouse**********Horse************www.lynchie.com/horse/uk
Lion************Cat**************www.lynchie.com/cat/uk
Mouse**********Elephant**********www.lynchie.com/mouse/uk

Do you want to end up with this (what it appears to me you're asking):
Column A ********Column B ******** Column C
Dog ************Dog************* www.lynchie.com/cat/UK
Mouse**********Horse************www.lynchie.com/horse/uk
Lion************Cat**************www.lynchie.com/cat/uk
Elephant**********Elephant**********www.lynchie.com/mouse/uk

Or this (what I think you mean):
Column A ********Column B ******** Column C
Cat ************Cat************* www.lynchie.com/cat/UK
Mouse**********Horse*************www.lynchie.com/horse/uk
Lion************Cat**************www.lynchie.com/cat/uk
Mouse**********Mouse************www.lynchie.com/mouse/uk

or neither?

[Lynchie]

errr... neither

Apologies - I did not depict this as clearly as I thought. There is no relationship between Column C and the other two columns.

A better way of depicting this would be:

Column A*****Column B
Dog*********Cat
Duck********Horse
Lion*********Fox
Mouse*******Cow

Column C
www.lynchie.com/duck/uk
www.lynchie.com/dog/uk
www.lynchie.com/mouse/uk
www.lynchie.com/lion/uk

Running through. Wherever the term 'dog' appears in Column C I want to replace it with the term 'cat'. Whenever the term 'duck' appears I want to replace it with 'horse'. This means the above would end up as:

Column A*****Column B
Dog*********Cat
Duck********Horse
Lion*********Fox
Mouse*******Cow

Column C
www.lynchie.com/horse/uk
www.lynchie.com/cat/uk
www.lynchie.com/cow/uk
www.lynchie.com/fox/uk

>> terabycomputer - does this make some more sense now?

Best Wishes,

Lynchie

[Rollin_Again]

Here you go. Just replace the portion in red to reflect the correct range of data in column A.

Sub ReplaceText()

For Each vCell In Range("A2:A100").Cells

Columns("C:C").Cells.Replace What:=vCell, Replacement:=vCell.Offset(0, 1).Value, LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

Next vCell

End Sub

Regards,
Rollin

[Lynchie]

Thanks Rollin - works like a dream!

Best Wishes,

Lynchie

[DMR1712]

This is exactly what I was looking for, thank you very, very much!

I however have another problem, My column A consists of entries that are formatted like this:

Example 1--------replace by "one"
Example 2--------replace by "two"
etc
Example 10-------replace by "ten"
etc
etc into the hundreds

Maybe you already see my problem, Changing Example 1 ofcourse also changes Example 10. This then becomes "one0" instead of "ten"

Is there a possibility to circumvent this? Maybe reverse the process (instead of top to bottom, bottom to top)?

Thanks in advance for your help.

Best,
DMR1712

[Rollin_Again]

Change the portion of the code that is highlighted in red so that is reads xlWhole

Columns("C:C").Cells.Replace What:=Range("A" & i).Value, Replacement:=Range("A" & i).Offset(0, 1).Value, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

I've also included macro code that will search from the bottom up. Just change the values in red to reflect your correct start and end rows.

Sub ReplaceString()

StartRow = 1
EndRow = 10

For i = EndRow To StartRow Step -1

Columns("C:C").Cells.Replace What:=Range("A" & i).Value, Replacement:=Range("A" & i).Offset(0, 1).Value, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Next i

End Sub

Regards,
Rollin

[DMR1712]

Brilliant! this worked like a charm!

thank you so much!