automatic find and replace

[richanor]

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

 

[estrauss22]

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]

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:

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