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.

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.

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" wrote in message
...
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.

Hi

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

--
Regards

Roger Govier


"Homer Jay" wrote in message
...
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" wrote in message
...
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.