Hi

You can do this with VBA code

Sub test()
Dim c As Range
For Each c In ActiveSheet.Range("a1:e10").Cells
If Left(c, 2) = "M2" Then
c = "TP" & Mid(c, 3, 255)
End If
Next
End Sub

Change the range "A1:E10" to suit the full range of the data where you
want to make the replacement.

You can copy the code as above and paste it into your Visual Basic
Editor
(VBE) in a Standard Module located in your file. To do this,
Alt + F11 (open VBE)
Ctrl + R (open Project Explorer)
Select the file name on the left
Insert Module
Paste code in Module
Amend code as desired
--
Regards

Roger Govier


"Homer Jay" wrote in message
...
I need some help in creating a look up to replace some data in my
spreadsheet. I had thought about using Access, but need to keep the
cell's in the format that they are in, and so cannot accurately import
the data into Access for manipulation.

A snapshot of my data is below:

A B C D E F
M2XX ABC DEF GHI
M2XY 123 456 789
M2XZ ZYX WVU RST

I want to be able to go down through the rows and where Excel find's
the characters M2 at the beginning of a cell/row to replace it with
(for example) TP. I can't just use the Find Replace function as
there are text fields which contain M2 in the middle of a description,
which I do not want to change. I only want to change the 2 characters
at the beginning of the field.

I can't change the layout of the spreadsheet, for example concatenate
all the fields to one long field and then look up and replace where M2
is at the beginning as I need to keep the layout intact. The M2 I need
to replace is always at the beginning of the row of data and can be in
any column, but is always the first 2 characters.

If any more information is needed please let me know, hopefully
someone can help.

Thanks.