VBA remove first character only if its a letter

[kevin440red]

I have this VAB that removes the first character it work well but I need to edit it to where it only removes the first character only if its a letter.
It is set to range.

Sub RemoveFirstThreeCharactersInEachCell()
For Each cell In Range("b6", Range("b1500").End(xlUp))
If Not IsEmpty(cell) Then
cell.Value = Right(cell, Len(cell) - 1)
End If

Next cell


For Each cell In Range("c6", Range("c1500").End(xlUp))
If Not IsEmpty(cell) Then
cell.Value = Right(cell, Len(cell) - 1)
End If

Next cell

For Each cell In Range("d6", Range("d1500").End(xlUp))
If Not IsEmpty(cell) Then
cell.Value = Right(cell, Len(cell) - 1)
End If

Next cell

For Each cell In Range("e6", Range("e1500").End(xlUp))
If Not IsEmpty(cell) Then
cell.Value = Right(cell, Len(cell) - 1)
End If

Next cell


Columns("A:E").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False



End Sub

 

[Eric W]

Try this:

If Not IsEmpty(cell) Then
    If Left(Cell.Value, 1) Like "[a-zA-Z]" Then
        cell.Value = Mid(cell.Value, 2)
    End If
End If

in 4 places.

 

[EXCEL MAX]

Hello Kevin440red,
format lines in For Each statement like this...

     If Not IsEmpty(cell) And Not IsNumeric(Left(cell, 1)) Then
        cell.Value = Right(cell, Len(cell) - 1)
    End If

 

[kevin440red]

Try this:

If Not IsEmpty(cell) Then
    If Left(Cell.Value, 1) Like "[a-zA-Z]" Then
        cell.Value = Mid(cell.Value, 2)
    End If
End If

in 4 places.

Excellent

 

[Eric W]

Glad we could help! Thanks for the feedback.

 

[Rick Rothstein]

Since your range is fixed and contiguous, this one-liner macro will remove the first character if that character is not a number...

Sub RemoveFirstAlphaOnly()
  [B6:E1500] = [IF({1},MID(B6:E1500,2-ISNUMBER(-LEFT(B6:E1500)),99))]
End Sub