# Excel Find and Replace Formula

Excel Excel 2007 home and student
April 24, 2010 at 15:12:56
Specs: Windows 7
 Hi,I have 3 columns:1st: New numbers with the 2nd column next to it.2nd: Old numbers to be replaced3rd: Random + Old numbers in different sheet.First I need to find each number from the Second column in Third column. Then replace that number with new number in first column same Row of the Old number(2nd column). I have the list with at least 12000 numbers. And have to replace each old number with a new one in the third column. Please be requested to provide me the formula or way to sort the same at once or in a quickest manner rather copying them each and replace. Thanks

See More: Excel Find and Replace Formula

#1
April 24, 2010 at 17:04:11
 Question 1:Is every Old number in the 2nd column going to be found in the 3rd column?Question 2:In the columns with the Old numbers, is there only one instance of each number?In other words does it look like this:``` New Old Random and Old 1 7 12 2 5 7 4 6 6 ```or this:``` New Old Random and Old 1 7 12 2 5 7 4 7 7 ```

Report •

#2
April 25, 2010 at 03:18:49
 1. Yes Every Old number (2nd Column) can be found in the 3rd Column.2. There is only one instance of old number in both 2nd and 3rd column each. Like you said New Old Random and Old 1 7 12 2 5 7 4 6 6Also would like to let you know that the 2nd & 3rd column has other 3 columns one on left of 2nd and 2 in the middle of both and 3 columns before the Random and Old numbers. In case required. Thanks

Report •

#3
April 25, 2010 at 04:04:16
 Well, since we can't see your spreadsheet from here, we can't tell exactly which columns contain the data and/or need to be changed. Telling us that there are other columns around the ones we are dealing with doesn't help. All we can do is make assumptions and provide the framework. You'll either need to modify the code below or tell us exactly which sheets and columns we're dealing with.These are the assumptions I am making in the following code:Sheet 1, Column A contains your New NumbersSheet 1, Column B contains your Old NumbersSheet 2, Column C contains the Random and Old numbers. This is the column that we are updating.Please note that macros can not be undone, so I strongly suggest that after you modify the code to match the actual columns you are using, test it in a backup copy of your workbook. ```Option Explicit Sub ReplaceOld() Dim lastOld, lastRndOld, nxtRow, oldNum As Integer Dim c 'Determine Last Row of Old Number List lastOld = Sheets(1).Range("B" & Rows.Count).End(xlUp).Row 'Determine Last Row of "Random and Old Number" List lastRndOld = Sheets(2).Range("A" & Rows.Count).End(xlUp).Row 'Loop through Old Numbers in Column B (2) For nxtRow = 1 To lastOld oldNum = Sheets(1).Range("B" & nxtRow) 'Find each Old number in "Random and Old Number" list With Sheets(2).Range("A1:A" & lastRndOld) Set c = .Find(oldNum, lookat:=xlWhole) If Not c Is Nothing Then 'When found, replace it with New number from Column A (1) Sheets(2).Range(c.Address) = Sheets(1).Range("A" & nxtRow) End If End With Next End Sub```

Report •

Related Solutions

#4
April 25, 2010 at 04:24:19
 Please find the exact list as follows:Action Item No New ItemIDRelist 280457343741 697 250619292778Relist 260570504013 0018 BR 250619292770Relist 250601974383 IF-10-8465 250601974383Relist 260587219455 300243 250619292790Relist 250602061223 IF-3-8633 250619292799Relist 260587219688 EDIE 250619292807Relist 280485213092 IF-11-8744 250619292808Relist 260531134768 BS STP FUN 250619292810Relist 280482647756 IF-10-8489 250619292821As shown above, there are 4 columns, 2nd is old and 4th is new list of numbers.012181 012181004002 39E610 BLACK SUED 4 250549537991012181 012181004005 39E610 BLACK SUED 7 250549538460014971 014971035005 312688 PURPLE 1 260527260880014722 014722002003 J05746 BLACK 8 260587219773015621 015621489004 IF-11-8744 FUCHSIA L 260589885361013420 013420203006 300996 ARMOR/TUR 11 280486425691009961 009961155001 45971 GREY 3 009961 009961155006 45971 GREY 8 009961 009961155007 45971 GREY 9 009961 009961155008 45971 GREY 10 009961 009961155009 45971 GREY 11 As shown above there are 6 columns in which 6th column needs to be changed. Some cells are empty and randomly arranged. I need to arrange the 6th column in text format when changed showing the numbers in the cells instead of the text. Thanks for the last reply, I would try if that works. Regards

Report •

#5
April 25, 2010 at 04:48:28
 Thank you for providing an example of the data, but I have one more thing to ask.It is hard to tell how your columns are lined up and since this question is extremely column dependent, we need to get that right. Above the comments box there is a little symbol that reads preIf you click on that symbol it will insert the pre tags in your post. If you paste your data between the pre tags, you can arrange the data in columns and they will stay lined up.Paste the data in between the pre tags and click the Preview Follow Up button. This will allow you see how the data lines up without actually submitting your post.If you don't like how the data lines up, you can edit your post below the preview window.To preview your post again, click in the box next to: `Check To Show Confirmation Page Again` and click either Confirm button. This will allow you keep checking your post until the data is lined up so that we can tell which column contains what.Once you are satisfied that the data lines up correctly, just click either Confirm button.Finally, if you include the sheet names and column letters, we might be able to provide a final solution. If not, we will have to assume sheet names and columns and you will have to modify the solution to match your workbook.

Report •

#6
April 25, 2010 at 05:02:43
 ``` Sheet1: Action Last Old Code New Relist 280457343741 697 250619292778 Relist 260570504013 0018 BR 250619292770 Relist 250601974383 IF-10-8465 250601974383 Relist 260587219455 300243 250619292790 Relist 250602061223 IF-3-8633 250619292799 Relist 260587219688 EDIE 250619292807 Relist 280485213092 IF-11-8744 250619292808 Sheet2: Random&old 012181 012181004002 39E610 BLACK SUED 4 250549537991 012181 012181004005 39E610 BLACK SUED 7 250549538460 014971 014971035005 312688 PURPLE 1 260527260880 014722 014722002003 J05746 BLACK 8 260587219773 015621 015621489004 IF-11 FUCHSIA L 260589885361 013420 013420203006 300996 ARMOR/TUR 11 280486425691 009961 009961155001 45971 GREY 3 009961 009961155006 45971 GREY 8 009961 009961155007 45971 GREY 9 ```

Report •

#7
April 25, 2010 at 06:56:54
 Hi I have sorted with lined up the above data now. Please see the last post.Apologize for the last messed up reply..Regards

Report •

#8
April 25, 2010 at 12:32:20
 Please look at the following code showing "Type Mismatch" as per the data i've shown above. ```Option Explicit Sub ReplaceOld() Dim lastOld, lastRndOld, nxtRow, oldNum As Integer Dim c 'Determine Last Row of Old Number List lastOld = Sheets(1).Range("B" & Rows.Count).End(xlUp).Row 'Determine Last Row of "Random and Old Number" List lastRndOld = Sheets(2).Range("F" & Rows.Count).End(xlUp).Row 'Loop through Old Numbers in Column F (2) For nxtRow = 1 To lastOld oldNum = Sheets(2).Range("F" & nxtRow) 'Find each Old number in "Random and Old Number" list With Sheets(2).Range("F1:F" & lastRndOld) Set c = .Find(oldNum, lookat:=xlWhole) If Not c Is Nothing Then 'When found, replace it with New number from Column D (1) Sheets(2).Range(c.Address) = Sheets(1).Range("B" & nxtRow) End If End With Next End Sub ```Thanks

Report •

#9
April 25, 2010 at 19:19:56
 OK, there are at least 3 things going on here:1 - To answer your direct question about the "Type Mismatch".You (we) are getting that because I dimensioned the variable oldNum as an Integer. Since VBA integers can only range from -32,768 to 32,767, your "numbers" (e.g. 280485213092) are too big to be integers.I have changed the code to consider them to be strings, since we are not really treating them as numbers.2 - I see that you made some modifications to the code. It appears that you are looking at the wrong columns in a couple of places.Since we are trying to find the the Old Numbers in the list of Random and Old, we need to search for the numbers in Sheet1 Column B and look for them in Sheet2 Column F,Therefore it's:`oldNum = Sheets(1).Range("B" & nxtRow) `not `oldNum = Sheets(2).Range("F" & nxtRow)`Then we want to replace them with the New numbers from Sheet1 Column D, so it's:`Sheets(2).Range(c.Address) = Sheets(1).Range("D" & nxtRow) `not```Sheets(2).Range(c.Address)= Sheets(1).Range("B" & nxtRow) ```3 - As far as I can tell, in the example data you provided, none of the numbers in the Rand and Old column need to be replaced, since none of them match any of the numbers in the Old numbers column. Is that correct?Anyway, based on your example, this code should work. Again, since it can't be undone, I suggest you try it in a backup copy of your workbook:```Option Explicit Sub ReplaceOld() Dim lastOld, lastRndOld, nxtRow Dim oldNum As String Dim c 'Determine Last Row of Old Number List lastOld = Sheets(1).Range("B" & Rows.Count).End(xlUp).Row 'Determine Last Row of "Random and Old Number" List lastRndOld = Sheets(2).Range("F" & Rows.Count).End(xlUp).Row 'Loop through Old Numbers in Column F (2) For nxtRow = 2 To lastOld oldNum = Sheets(1).Range("B" & nxtRow) 'Find each Old number in "Random and Old Number" list With Sheets(2).Range("F2:F" & lastRndOld) Set c = .Find(oldNum, lookat:=xlWhole) If Not c Is Nothing Then 'When found, replace it with New number from Column D (1) Sheets(2).Range(c.Address) = Sheets(1).Range("D" & nxtRow) End If End With Next End Sub ```

Report •

#10
April 26, 2010 at 03:08:54
 Great it is working now .. I'll be back after testing all files. .Cheers

Report •

#11
April 26, 2010 at 03:24:15
 Thanks a lot it works!!Also I would like to know what is the best way to see the numbers in text font. For instance The number shown below is in Number format 250619292778When change this to the text format it comes as2.50619E+11To look this as number I have to F2 every cell and then it shows as 250619292778.I need to change the whole column (12000 cells) at once to finally see them in number format while applying the text format on them. Thanks & Regards

Report •

#12
April 26, 2010 at 04:14:41
 re: I need to change the whole column (12000 cells) at once to finally see them in number format while applying the text format on them. This doesn't make sense. If you look under the Format...Cells menu, you'll see that there is a Number format (with various options) and a Text format. Your values can't be formatted as both.Try this: Select to entire column, choose Format...Cells and pick the format that you want. What you choose (Text or Number) depends on what you plan to do with the values later.

Report •