How to replace a list of different words with a specific word in excel? Experts help req

[Abbas999]

Hi Experts,

I am really in need of yours help. I am using Excel 2007 Version. I have around 3000 rows of data in excel and in each row there is a text "AB10" I want all of "AB10" replaced with different series of words.

"AB10" Replaced with "A1"
"AB10" Replaced with "A2"
"AB10" Replaced with "A3"
"AB10" Replaced with "A4"
"AB10" Replaced with "A5"
"AB10" Replaced with "A6"

and so on for 3000 rows.

I believe that experts will have solution for this problem.

Note - I'm newbie!

Thanks in advance

 

[GerryZ]

@Abbas999
did you see post #9?
i belive is the solution
let me know

 

[Abbas999]

@Abbasss999
this my solution
let me know if fit you!

	A	B
<what>bars</what><where><where>AB10</where></where>	<what>bars</what><where><where>Z10</where></where>

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula B1 =REPLACE(A1,SEARCH("AB",A1),2,"Z") <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web - Excel Jeanie Html 4

@GerryZ Thanks you for your reply. But this will not help me.

here is what i want.

[TABLE="width: 500"]
<tbody>[TR]
[TD]OLD Line[/TD]
[TD]Zip Codes[/TD]
[TD]I want this final line[/TD]
[/TR]
[TR]
[TD]<Where>AB10</Where>[/TD]
[TD][TABLE="width: 83"]
<tbody>[TR]
[TD="width: 83"]CA1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]<Where>CA1</Where>[/TD]
[/TR]
[TR]
[TD]<Where>AB10</Where>[/TD]
[TD][TABLE="width: 83"]
<tbody>[TR]
[TD="width: 83"]EC4P[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]<Where>EC4P</Where>[/TD]
[/TR]
[TR]
[TD]<Where>AB10</Where>[/TD]
[TD][TABLE="width: 83"]
<tbody>[TR]
[TD="width: 83"]DA16[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]<Where>DA16</Where>[/TD]
[/TR]
[TR]
[TD]<Where>AB10</Where>[/TD]
[TD][TABLE="width: 83"]
<tbody>[TR]
[TD="width: 83"]G73[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]<Where>G73</Where>[/TD]
[/TR]
</tbody>[/TABLE]

Is there a way to tell excel to Copy data from Column B "Zip Codes" and Enter it in column 3 between <Where></Where>

thank you

 

[GerryZ]

MAYBE:
<table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:14pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="width:30px; " /><col style="width:368.8px;" /><col style="width:424px;" /></colgroup><tr style="background-color:#cacaca; text-align:center;font-size:8pt; "><td > </td><td style=" border-width:3px; border-style:solid;border-color:#f0f0f0; ">A</td><td style=" border-width:3px; border-style:solid;border-color:#f0f0f0; ">B</td></tr><tr style="height:21px ;" ><td style="border-width:3; border-style:solid;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >20</td><td ><what>bars</What><Where><Where>AB10</Where></td><td ><what>bars</What><Where><Where>ZA1</Where></td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B20</td><td >=REPLACE(A20,SEARCH<span style=' color:008000; '>("AB",A20)</span>,4,"ZA"&ROWS<span style=' color:008000; '>(B$20:B20)</span>)</td></tr></table></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4

 

[Abbas999]

MAYBE:

	A	B
<what>bars</what><where><where>AB10</where></where>	<what>bars</what><where><where>ZA1</where></where>

<colgroup><col style="width:30px; "><col style="width:368.8px;"><col style="width:424px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]20[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula B20 =REPLACE(A20,SEARCH("AB",A20),4,"ZA"&ROWS(B$20:B20)) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web - Excel Jeanie Html 4

not working

 

[MARK858]

Based on your DROPBOX spreadsheet layout... basically it is what Andrew gave you before you made clear you were substituting from another column....

In B2

=SUBSTITUTE(A2,"AB10",C2)

 

[GerryZ]

@Abbas999


https://www.dropbox.com/s/yer3a0akygw17ii/Cartel1.xlsx?dl=0

 

[Abbas999]

Another pic for understanding my need.

 

[Abbas999]

@Abbas999


https://www.dropbox.com/s/yer3a0akygw17ii/Cartel1.xlsx?dl=0

It looks good. But the zip codes are not in 1,2,3,4 format. Here is the pic to understand more about my problem.

 

[Abbas999]

Based on your DROPBOX spreadsheet layout... basically it is what Andrew gave you before you made clear you were substituting from another column....

In B2

I think this is working @MARK858 But i am a newbie and i don't know how to run a formula. I have copy the formula "=SUBSTITUTE(A2,"AB10",C2)" and it converts th First row only how to do that for 3000 rows? Column C have all the zip codes and Column A have the data that needs to be replaced with zip codes. Please tell me what i am doing wrong?

 

[MARK858]

If there is something in every row in Column A hover your cursor over the bottom right corner of the cell with the formula, then when then cursor changes to a black + double left click and it will autofill the column.

If you have blank cells in column A then when it changes to a + left click & hold and drag down.