Excel Find and Replace part of text in cell with a meaning from another worksheet

**jiwaniakbar** · 10-14-2013, 06:23 AM

I have a text in Column A in say sheet1 and I have some words listed in sheet2 column A and its description in Column B.

for every word in the text in column A of sheet1 I would like to lookup Column A of Sheet2 and replace with Description in Column B

Lets Say in Sheet1, A1 = If Var1 is equal to Var2

Sheet 2
A B
1 var1 Variable1
2 Var2 Variable2

A1 in Sheet1 should be replaced to A1= If Variable1 is equal to Variable2

Is there a way to do it. I tried substitute but it just cant lookup range of cells.

**AlKey** · 10-14-2013, 06:30 AM

	A	B	C
1	var1	Variable1	=SUBSTITUTE(A1,"var1","Variable1")
2	var2	Variable2	=SUBSTITUTE(A2,"var2","Variable2")

You can also try this:

=IF(SEARCH("var1",A1),SUBSTITUTE(A1,"var1","Variable1"),"")

**6StringJazzer** · 10-14-2013, 06:47 AM

Welcome to the Forum!

Do you want to replace only part of the text in column A of sheet1? Or the entire cell?

Do you want the result to actually replace the cells in column A of sheet1, or do you want a separate column with the result?

If you want to replace the entire contents of the cell, and you can have the results in column B, then you can use this formula in column B:

Formula:

Please Login or Register  to view this content.

I've attached an example.

However, if you need to replace only a part of the text (as described in your thread title), I think a macro is required. If you want to replace it in the same cell instead of using another column, a macro is definitely required.

**jiwaniakbar** · 10-14-2013, 07:42 AM

Thanks for the quick reply.

I have checked the sample excel. Unfortunately, my requirement is little Complicated. I think i need a macro but not sure how to right.

1. Yes. I want to replace only part of the text. So if text is "F1 is equal to F2 plus F3" where F1,F2,F3 are listed in another worksheet with description as Field1, Field2, Field3 then updated text should read as "Field1 is equal to Field2 plus Field3". So I have multiple words to replace in same cell.
2. It is okay if it replaces in the same cell. Need not put it in a new cell.

I hope I am able to express requirement clearly.

**6StringJazzer** · 10-14-2013, 08:43 AM

Here is an example where this is done with a macro.

Note that you have to worry about a special condition here, even if you do it manually. Suppose you have some occurrences of F1 and some F10. When you scan for F1 it's also going to find F1 within F10 and change "F10" to "Field10" which may not be what you want depending on what your real strings are.

Edit: For example, you might want to change "Cook" to "Bake" but it would also change "Cookie" to "Bakie".

**jiwaniakbar** · 10-14-2013, 09:04 AM

Thanks...
I see the point what you are saying about exceptions. This will have a problem for me because I will run into such cases. For example I have a words like
ws-wrk -Working Storage
ws-wrk-st -Working Storage Standard
ww-wrk-pt -Working Storage Pointed

Text to replace: ws-wrk = ws-wrk-st + ws-wrk-pt
Replaced Text: working storage 1 is equal to working storage 1-st + working storage 1-pt

Which is not i expect.
Is there a way to replace when there exact word match?

**6StringJazzer** · 10-14-2013, 02:09 PM

Here is a new and improved version that should cover all cases. If this doesn't solve your situation please attach a file with sample data.

**jiwaniakbar** · 10-14-2013, 04:21 PM

Thanks!..this is great. I will run it on real data

and verify.... thnx again

Excel Find and Replace part of text in cell with a meaning from another worksheet

LinkBack

Thread Tools

Rate This Thread

Display

Excel Find and Replace part of text in cell with a meaning from another worksheet

Re: Excel Find and Replace part of text in cell with a meaning from another worksheet

Re: Excel Find and Replace part of text in cell with a meaning from another worksheet

Re: Excel Find and Replace part of text in cell with a meaning from another worksheet

Re: Excel Find and Replace part of text in cell with a meaning from another worksheet

Re: Excel Find and Replace part of text in cell with a meaning from another worksheet

Re: Excel Find and Replace part of text in cell with a meaning from another worksheet

Re: Excel Find and Replace part of text in cell with a meaning from another worksheet

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] need VBA to find and replace part of a value in a cell

Find and Replace Text in Formula on a given worksheet in an excel workbook using VBA

Find and Replace Part of cell

Find part of text within cell and replace from another cell

[SOLVED] Find and replace part of a text string

Bookmarks

Bookmarks

Posting Permissions