automatic find and replace

richanor

Active Member
Joined
Apr 8, 2006
Messages
285
Hi

I was wondering if somebody could point me in the right direction: I am frequently receiving large spreadsheets of customer information (1000 - 5000 rows) - beginning with their postcode in column A. I was wondering if it is possible to automatically replace the postcode with the town, for example:

replace 'EH01, EH02, EH03' with 'Central Edinburgh'
replace 'EH04, EH05, EH06' with 'South Edinburgh' etc.

One potential problem is that, although the postcode prefixes are always the same, they are sometimes followed by other information, for example, some cells will say 'EH01 2HH' and some will say 'EH01 3FG lothian'. The second part of the postcode and any other information is irrelevant - I would want to replace any postcode beginning 'EH01' with 'Central Edinburgh'

Also as I don't know in advance of receiving the spreadsheet what postcodes are going to be contained in it, could I enter some information on sheet 2 about which postcodes I would I would like to replace with which town names, and use this? For example - on sheet 2:

A1 central edinburgh
A2 EH01
A3 EH02
A4 EH04

B1 south edinburgh
B2 EH04
B3 EH05
B4 EH06

Any help / pointers with this would be greatly appreciated. Thanks

Rich
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

estrauss22

New Member
Joined
Jul 5, 2007
Messages
32
I would use a

=left(cell, number of characters)

to capture the correct number of characters.

In a separate file, I would maintain a master table of all the information you needed. i.e. -

column 1 = EH01

column 2 = Central Edinburgh

Then, each time you receive your file, insert a row and build a lookup that is aimed at your master table.

=VLOOKUP(Cell, Lookup Table, column, false)

The formula reads as - look at information in a cell (of the file you received) cell # that has "EH01", find that in the master table (highlight the table), and tell me what is in colum two. It will find the information and place it in the formula row.

Hope this helps..
 

richanor

Active Member
Joined
Apr 8, 2006
Messages
285
Firstly, thanks for th reply and sorry for the delay! I have had no internet access for a week.

My initial idea was to try to piece together a bit of code to do this - because I would like to be able to apply this to various other workbooks that I use, and the it becomes quite long winded using formulas.

I have got a peice of code which kind of does what I want. If anybody could give me a pointer in adapting this - it would be most appreciated:

Code:
Sub FindReplace() 

Dim x As String 
Dim y As String 

x = ActiveSheet.Range("A1") 
y = ActiveSheet.Range("B1") 

With ActiveSheet.Range("C1:E100") 
   .Replace What:=x, Replacement:=y 
End With 

End Sub

This obviously takes what is in A1 searches the selected range and replaces any matches with B1. How do I then tell excel to move onto cell A2 and repeat, then keep going until it finds a blank cell and end?

Is it also possible to adapt the code so that the list of find and replace words (in columns A and B) are on sheet 2, but ask excel to search the given range in sheet 1?

Again, thanks in advance for your help,

Rich
 

Watch MrExcel Video

Forum statistics

Threads
1,130,112
Messages
5,640,174
Members
417,130
Latest member
Darion2021

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