Find and Replace Text within a cell

[Dan_B]

Hi Everyone

I have a massive code list of 8000 items which I need to change specific entries within the string to a shorter code

For example if the cell contains "DRINK FIZZY NICE BEVERAGE 10 PACK" I need it to say "DRINK FIZZY NICBVRG 10 PACK" so that the description now contains the code NICBVRG

I have found this code which replaces the entire cell with the code instead of just changing the specified description in the string - please help as I really wouldn't like to have to go through line by line 8000 times - Many thanks all

Please Login or Register  to view this content.

[Marc L]

Hi,

in VBA inner help of Range.Replace method, just see its parameters (LookAt) …

[JOHN H. DAVIS]

Does this help?

Please Login or Register  to view this content.

[Dan_B]

Thanks Marc L, much appreciated however I am not 100% sure that this code works correctly as I've tried it but it appears to only replace the values with the items next to them

Are you able to help with a code that will actually provide what I'm looking for? A find / replace macro for text combinations within a string of text as per my example

My apologies as I am not very good with VBA (total newbie)

Thanks

[Dan_B]

Hi John H Davis

Thanks for the response.. I think I may have left out some other key info..

In column A, I have a list of 8000 lines containing the descriptions (or similar) to "DRINK FIZZY NICE BEVERAGE 10 PACK"
In Column B, I have a code list which contains the "NICBVRG" as well as other codes that will need to be replaced in Column A

Hopefully that provides more clarity on what I'm trying to achieve

Thanks again

[bakerman2]

Word of advice.
Load both ranges in Array, performe replacement in memory and write changed Array back to Range. This will shorten execution time signifficantly.
Please post example file for us that represents actual situation to work with.

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

[Dan_B]

Here is the file - i've added notes but here's a summary

Column A contains the original partially coded descriptions
Column B contains what description should be replaced in Column A
Column C contains the code that should be present in Column A

If more info is required let me know

Thanks everyone for your help

P.S. I've not included my original code as I do not think it is quite correct and I'm sure you will have much better examples/versions than me

[bakerman2]

Try this for starters. Results are displayed in column E.

Please Login or Register  to view this content.

[JOHN H. DAVIS]

Try:

Please Login or Register  to view this content.

[Dan_B]

Hi John H. Davis

Worked perfectly for what I was trying to achieve

Thanks also to Bakerman2 for your help as I know I'll be able to use what I've learned for other projects

[JOHN H. DAVIS]

You're welcome. Glad to help out and thanks for the feedback. Please comply with Forum Rules and mark this thread as SOLVED. Refer too link for guidance.

http://www.excelforum.com/forum-rule...rum-rules.html