Stuart,

I think this is it

Dim C As Range

With ActiveSheet
For Each C In Intersect(Range("E:E"), ActiveSheet.UsedRange)
Select Case True
Case LCase(Right(C, 2)) = "m2":
C.Value = Left(C.Value, Len(C.Value) - 2)
C.Cells(, 9 - C.Column + 1) = "m2"
'Case "LCase(Right(C, 1)) = "something else":
End Select
Next
End With

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Stuart" wrote in message
...
Many thanks, big help.

In the other tests, as far as I can see, the test will similarly
involve testing the end of the string in col E, but for
a different length of characters.

So I may have to test for last char 'm'
or say 'tonne'.

Is there a way to incorporate these conditions into the
Case construct, please?

regards.

"Bob Phillips" wrote in message
...
Stuart,

Here's some code

Dim C As Range

With ActiveSheet
For Each C In Intersect(Range("E:E"), ActiveSheet.UsedRange)
Select Case LCase(Right(C, 2))
Case "m2":
C.Value = Left(C.Value, Len(C.Value) - 2)
C.Cells(, 9 - C.Column + 1) = "m2"
'Case "something else":
End Select
Next
End With


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Stuart" wrote in message
...
Ideally I need to test each cell as follows:

if the last 2 character are "m2" then copy it to col I, same row
and strip it from the 'found' cell.

Many similar checks to run, but if you would be kind enough
to example one, maybe I can solve those remaining

Regards.

----- Original Message -----
From: "Bob Phillips"
Newsgroups: microsoft.public.excel.programming
Sent: Saturday, February 28, 2004 4:38 PM
Subject: Right & Split question


What is the question?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Bob Phillips" wrote in message
...

"Stuart" wrote in message
...
I sample cells in col E, for data that ends as "m2":
Because I do not know this data. I look for the
last 2 characters:

Dim C As Range, x As Variant

With ActiveSheet
For Each C In Intersect(Range("E:E"), ActiveSheet.UsedRange)
If Right(C, 2) = "m2" Then
x = Split(C, " ")
'do my stuff
End If
Next
End With

Help please.

Regards.



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.596 / Virus Database: 379 - Release Date: 26/02/2004






---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.596 / Virus Database: 379 - Release Date: 26/02/2004






---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.596 / Virus Database: 379 - Release Date: 26/02/2004