automatic find and replace

richanor

Active Member
Joined
Apr 8, 2006
Messages
291
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

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

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
291
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,708
Messages
5,855,257
Members
431,717
Latest member
Ibyb

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