Trim left characters excel vba

[Apple08]

Hi everyone

I have a problem to trim the left characters of a cell, if the column E cell value starts with P or M, then the first left two characters have to trim off, if it starts with X, then the left four characters have to trim off.

I have done the vba as follows but it doesn't work, please can anyone help?

Dim i As Long
Dim lastrow As Long
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lastrow
If Cells(i, 5).Value Like "P-*" Or Cells(i, 5).Value Like "M-*" Then
Cells(i, 5).value = Right((i,5), Len((i,5) - 2)
Else
If Cells(i, 5).Value Like "Z*" Then Cells(i, 5).Value = Right((i,5), Len(i,5) - 4)
End If
End If
        
    Next I

 

[wideboydixon]

Dim thisRow As Long
Dim lastRow As Long
lastRow = Cells(Rows.Count, 1).End(xlUp).Row

For thisRow = 2 To lastRow
    If Left$(Cells(thisRow, 5).Value, 2) = "P-" Or Left$(Cells(thisRow, 5).Value, 2) = "M-" Then
        Cells(thisRow, 5).Value = Mid$(Cells(thisRow, 5).Value, 3)
    ElseIf Left$(Cells(thisRow, 5).Value, 1) = "Z" Then
        Cells(thisRow, 5).Value = Mid$(Cells(thisRow, 5).Value, 5)
    End If
Next thisRow

WBD

 

[Apple08]

It works perfectly! Many thanks!

 

[Rick Rothstein]

Here is another macro that should also work...

[table="width: 500"]
[tr]
	[td]Sub Test()
  Dim Addr As String
  Addr = "A2:A" & Cells(Rows.Count, "A").End(xlUp).Row
  Range(Addr) = Evaluate(Replace("IF((LEFT(@)=""P"")+(LEFT(@)=""M""),MID(@,3,LEN(@)),IF(LEFT(@)=""X"",MID(@,5,LEN(@)),IF(@="""","""",@)))", "@", Addr))
End Sub[/td]
[/tr]
[/table]

 

[Apple08]

Thanks Rick, it's so good to learn more options.