Substituting texts from specified list

AdarsH

New Member
Joined
Dec 1, 2009
Messages
14
Hello.

Am new to excel and i need some help for an excel formula. Below is the scenario:

Got the following data in different cells:

datax.jpg


And on a different sheet in the same workbook, have 45 different values to be converted:

values.jpg


What is required?

Knowing that using a VLOOKUP function, i can compare a value in the first cell to the list and return the desired value, i need to substitute the strings of characters with those the the list of 45 values so that i can proceed with my work.

Basically, for the 1st Cell for eg..

2232-2232-

I will need a formula to replace "2232-" by the value which a VLOOKUP function will give when comparing to the list of 45.

Result shall look like below:

20-20-

Looks simple when have the same set of characters in the cell, but for a cell which has multiple sets:

Code:
2210-22G1-45R1-45G1-20R1-20GP-

It becomes difficult for me. What i need exactly is:

Formula to substitute using the vlookup function so that it performs a lookup of the value in the specified cell with the list of 45 values and returns the value (20- or 40-) to be substituted in the cell. What i need finally is a set of 20- & 40- replacing the sets of present data.

Your help will be much appreciated.
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

sanrv1f

MrExcel MVP
Joined
Jan 1, 2009
Messages
3,474
Office Version
  1. 2016
Platform
  1. Windows
Welcome to the board,

It would be almost impossible to do it with formula, you can try this


goto VBE (Alt + F11)
Insert -> Module
Place the code
Quit VBE (Alt + Q)
Run the macro (Alt + F8)

Code:
Sub replce()
Dim cl As Range
For Each cl In Worksheets("Sheet2").Range("A1:A45")
    Worksheets ("Sheet1")
    Range("BE1:BE100").Replace cl, cl.Offset(, 1)
Next
End Sub

Sheet1 is the data sheet, sheet2 is where the find and replace list is stored

also note that, In future, It would be much better to post sample data tables instead of image, since it would be hard for the helpers to reproduce the problem in their sheets.
 

texasalynn

Well-known Member
Joined
May 19, 2002
Messages
8,458
would be easier to help you if your examples weren't pictures, so the data can be copied. Try using Jenny or the beta from Richard Schollar
 

AdarsH

New Member
Joined
Dec 1, 2009
Messages
14
Am new to the board. Thanks for your initial help. I'll try to use the add-ins to provide with sample data.
 

AdarsH

New Member
Joined
Dec 1, 2009
Messages
14

ADVERTISEMENT

Here are the data sets for sheet 1:

*This is only part of the file. Have 100s of lines like these with multiple values as defined in tables for sheet 2

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:180px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td >*</td><td >BE</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >CONTAINER GROUP</td></tr><tr style="height:34px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >20GP-40HC-20GP-</td></tr><tr style="height:68px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >2210-2210-2210-</td></tr><tr style="height:34px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >2210-2210-2210-2210-2210-</td></tr><tr style="height:34px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">2210-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">2210-</td></tr><tr style="height:34px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">2210-</td></tr><tr style="height:34px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:right; ">2232-</td></tr><tr style="height:68px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:right; ">4510-</td></tr><tr style="height:34px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td >4510-4510-</td></tr><tr style="height:34px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="text-align:right; ">2210-</td></tr><tr style="height:34px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="text-align:right; ">4510-</td></tr><tr style="height:51px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td >2210-2210-</td></tr><tr style="height:68px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="text-align:right; ">2210-</td></tr><tr style="height:34px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="text-align:right; ">2210-</td></tr><tr style="height:34px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="text-align:right; ">2210-</td></tr><tr style="height:34px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td style="text-align:right; ">2210-</td></tr><tr style="height:34px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td style="text-align:right; ">2210-</td></tr><tr style="height:34px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td style="text-align:right; ">2210-</td></tr><tr style="height:68px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td style="text-align:right; ">2210-</td></tr><tr style="height:34px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >21</td><td >2210-2210-2210-2210-2210-2210-2210-</td></tr><tr style="height:51px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >22</td><td >2210-2210-2210-2210-2210-2210-2210-2210-</td></tr><tr style="height:34px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >23</td><td >2210-2210-2210-2210-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >24</td><td style="text-align:right; ">2210-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >25</td><td style="text-align:right; ">2210-</td></tr><tr style="height:34px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >26</td><td >2210-2210-2210-2210-2210-</td></tr><tr style="height:51px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >27</td><td >2210-2210-2210-2210-2210-</td></tr><tr style="height:68px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >28</td><td style="text-align:right; ">2210-</td></tr><tr style="height:68px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >29</td><td >2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-</td></tr><tr style="height:102px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >30</td><td >2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-</td></tr><tr style="height:546px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >31</td><td >2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-</td></tr></table><br />

and below are the values in the tables:

<b>Sheet2</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td >*</td><td >A</td><td >B</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="text-align:right; ">2065-</td><td style="text-align:right; ">20-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">2100-</td><td style="text-align:right; ">20-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">2210-</td><td style="text-align:right; ">20-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">2215-</td><td style="text-align:right; ">20-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">2232-</td><td style="text-align:right; ">20-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">2251-</td><td style="text-align:right; ">20-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">2261-</td><td style="text-align:right; ">20-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:right; ">2270-</td><td style="text-align:right; ">20-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:right; ">2332-</td><td style="text-align:right; ">20-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="text-align:right; ">2430-</td><td style="text-align:right; ">20-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="text-align:right; ">4200-</td><td style="text-align:right; ">40-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="text-align:right; ">4310-</td><td style="text-align:right; ">40-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="text-align:right; ">4351-</td><td style="text-align:right; ">40-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="text-align:right; ">4361-</td><td style="text-align:right; ">40-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="text-align:right; ">4430-</td><td style="text-align:right; ">40-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="text-align:right; ">4510-</td><td style="text-align:right; ">40-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td style="text-align:right; ">4532-</td><td style="text-align:right; ">40-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td >20DV-</td><td style="text-align:right; ">20-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td >20GP-</td><td style="text-align:right; ">20-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td >20GP-</td><td style="text-align:right; ">20-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >21</td><td >20R1-</td><td style="text-align:right; ">20-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >22</td><td >20RE-</td><td style="text-align:right; ">20-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >23</td><td >22G0-</td><td style="text-align:right; ">20-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >24</td><td >22G1-</td><td style="text-align:right; ">20-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >25</td><td >22G2-</td><td style="text-align:right; ">20-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >26</td><td >22P3-</td><td style="text-align:right; ">20-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >27</td><td >22R1-</td><td style="text-align:right; ">20-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >28</td><td >22U1-</td><td style="text-align:right; ">20-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >29</td><td >22V0-</td><td style="text-align:right; ">20-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >30</td><td >40DR-</td><td style="text-align:right; ">40-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >31</td><td >40GP-</td><td style="text-align:right; ">40-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >32</td><td >40HC-</td><td style="text-align:right; ">40-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >33</td><td >40HQ-</td><td style="text-align:right; ">40-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >34</td><td >40RE-</td><td style="text-align:right; ">40-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >35</td><td >40RQ-</td><td style="text-align:right; ">40-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >36</td><td >40U1-</td><td style="text-align:right; ">40-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >37</td><td >42G1-</td><td style="text-align:right; ">40-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >38</td><td >42P3-</td><td style="text-align:right; ">40-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >39</td><td >42U0-</td><td style="text-align:right; ">40-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >40</td><td >45G0-</td><td style="text-align:right; ">40-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >41</td><td >45G1-</td><td style="text-align:right; ">40-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >42</td><td >45R1-</td><td style="text-align:right; ">40-</td></tr></table> <br /><br />
 

AdarsH

New Member
Joined
Dec 1, 2009
Messages
14
any clue how to do it? tried the VBA sanrv1f gave, but not working :S
 

sanrv1f

MrExcel MVP
Joined
Jan 1, 2009
Messages
3,474
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

thats an error on my part, apologies,

try this

Code:
Sub replce()
Dim cl As Range
For Each cl In Worksheets("Sheet2").Range("A1:A45")
    Worksheets("Sheet1").Range("BE1:BE100").Replace What:=cl, Replacement:=cl.Offset(, 1), LookAt:=xlPart
Next
End Sub
 

AdarsH

New Member
Joined
Dec 1, 2009
Messages
14
Hi Sankar. I tired the secod VBA code, still nothing. The code runs, but does not replace anything.
 

sanrv1f

MrExcel MVP
Joined
Jan 1, 2009
Messages
3,474
Office Version
  1. 2016
Platform
  1. Windows
did you change the Sheet names and the range to suit the actual data?
 

AdarsH

New Member
Joined
Dec 1, 2009
Messages
14
Tried changing range "BE1:BE100" to "BE:BE" for the whole column and "A1:A45" to "A1:A42", but no vain. Please check you email, i sent you the whole file.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,047
Messages
5,599,497
Members
414,315
Latest member
Yolanda5050

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top