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 replaced
3rd: 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

Report •

#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 6

Also 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 Numbers
Sheet 1, Column B contains your Old Numbers
Sheet 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 ItemID
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
Relist 260531134768 BS STP FUN 250619292810
Relist 280482647756 IF-10-8489 250619292821


As shown above, there are 4 columns, 2nd is old and 4th is new list of
numbers.

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-8744 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
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 pre

If 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

250619292778

When change this to the text format it comes as

2.50619E+11

To 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 •

Ask Question