Lookup and Replace

[Homer Jay]

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.

 

[Roger Govier]

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

 

[Roger Govier]

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

 

[Homer Jay]

Excellent. That works 99% perfectly........I have one question which will
make it 100% perfect. Is there any way the code can be edited so that once
it finds an M2 on a row it automatically moves to the next row?

For example if in cell A1 I have the data M2ABCDEF and in column C1 I have
M2Name but I want to keep M2Name as is and only change M2ABCDEF (to
TPABCDEF). If this is not possible it does not matter, I can't think of any
instances where I have M2 at the beginning that does not need to be changed,
but there may be 1 or 2 records like this.

Either way, thanks again for the code. I had opened up the VB editor but did
not really know where to start. Much appreciated.

 

[Homer Jay]

Excellent. That works 99% perfectly........I have one question which will
make it 100% perfect. Is there any way the code can be edited so that once
it finds an M2 on a row it automatically moves to the next row?

For example if in cell A1 I have the data M2ABCDEF and in column C1 I have
M2Name but I want to keep M2Name as is and only change M2ABCDEF (to
TPABCDEF). If this is not possible it does not matter, I can't think of any
instances where I have M2 at the beginning that does not need to be changed,
but there may be 1 or 2 records like this.

Either way, thanks again for the code. I had opened up the VB editor but did
not really know where to start. Much appreciated.

 

[Roger Govier]

Hi

Change
If Left(c, 2) = "M2" Then
to
If Left(c, 2) = "M2" And UCase(Left(c, 7)) <> "M2NAME" Then

 

[Roger Govier]

Hi

Change
If Left(c, 2) = "M2" Then
to
If Left(c, 2) = "M2" And UCase(Left(c, 7)) <> "M2NAME" Then