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

 

[Andrew Poulsom]

Welcome to MrExcel.

Is your data in a contiguous range? What is that range? Is there any other text in the cells?

 

[Abbas999]

Welcome to MrExcel.

Is your data in a contiguous range? What is that range? Is there any other text in the cells?

Each row has the same line of data.

Current rows are :
1. "<What>bars</What> <Where>AB10</Where>"<where>
</where>2. "<What>bars</What> <Where>AB10</Where>"
3. "<What>bars</What> <Where>AB10</Where>"
4. "<What>bars</What> <Where>AB10</Where>"
5. "<What>bars</What> <Where>AB10</Where>"


I want to replace all AB10 with other words e.g A1 to A1000

It will look like this after replacing:

1. <where>"<What>bars</What> <Where>A1</Where>"
</where>2. "<What>bars</What> <Where>A2</Where>"
3. "<What>bars</What> <Where>A3</Where>"
4. "<What>bars</What> <Where>A4</Where>"
5. "<What>bars</What> <Where>A5</Where>"

Thanks

 

[GerryZ]

Hello have you tried the tool Text to column?
Or in Excel 2013 the tool Autofill?

 

[Abbas999]

Hello have you tried the tool Text to column?
Or in Excel 2013 the tool Autofill?

@GerryZ The problem is not related to "text to columns", There should be a formula or a code that will find all "AB10" and replace them with series of number e.g Replace first row with
A1
then A2, A3, A4 and so on.
Here is the screenshot that will make it more easy to understand. Thanks

 

[Andrew Poulsom]

Formula in B1 copied down:

Excel 2010
	A	B
1	bars AB10	bars A1
2	bars AB10	bars A2
3	bars AB10	bars A3
4	bars AB10	bars A4
5	bars AB10	bars A5
Sheet1

Cell Formulas
Range	Formula
B1	=SUBSTITUTE(A1,"AB10","A"&ROWS(B$1:B1))

 

[Abbas999]

Formula in B1 copied down:

Excel 2010

	A	B
1	bars AB10	bars A1
2	bars AB10	bars A2
3	bars AB10	bars A3
4	bars AB10	bars A4
5	bars AB10	bars A5

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula B1 =SUBSTITUTE(A1,"AB10","A"&ROWS(B$1:B1)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Can you please also tell me how to use it? I am totally a newbie in excel. I have a list of codes.
From A1 to A2000
Will the formula replace all of them? Thanks

 

[Andrew Poulsom]

Enter the formula in B1 and copy it down.

 

[GerryZ]

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

<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:415.2px;" /><col style="width:417.6px;" /></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; " >1</td><td ><what>bars</What><Where><Where>AB10</Where></td><td ><what>bars</What><Where><Where>Z10</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 >B1</td><td >=REPLACE(A1,SEARCH<span style=' color:008000; '>("AB",A1)</span>,2,"Z")</td></tr></table></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4

 

[Abbas999]

Enter the formula in B1 and copy it down.

@Andrew Sorry I am unable to do that. Maybe something went wrong.
Please check the attached excel sheet. It has 3 Columns.

Dropbox link - https://www.dropbox.com/s/57pn82v6qldmx2s/Sheet.xlsx?dl=0

Column 1 has the data line which i want to get replaced.
Column 2 is empty
Column 3 has all the zip codes. I want the Column Zip codes to be entered between "<!-- What--> < Where>AB10< /Where>. there all AB10 will have to replace with zip codes. I am unable to run formula. Thanks