VBA delete characters from cell

[neodjandre]

Hi,

I am looking for a macro which deletes the first 3 characters of a text string in a cell but only if the first character begins with the letter "A".

could someone help?

thanks
andy

 

[Jonmo1]

Does it have to be a macro ?

=IF(LEFT(A1,1)="A",REPLACE(A1,1,3,""),A1)

 

[neodjandre]

jonmo, yes it does... sorry for this!

 

[njimack]

Sub test()
With Sheets("Sheet1").Range("A1")
If Left(.Value, 1) = "A" Then .Value = Right(.Value, Len(.Value) - 3)
End With

End Sub

 

[Jonmo1]

=IF(LEFT(A1,1)="A",REPLACE(A1,1,3,""),A1)
translated to VBA

If Left(Range("A1"),1) = "A" Then 
    Range("A1").Value = WorksheetFunction.Replace(Range("A1").Value, 1, 3, "")
End if

 

[Uchiha_Itachi]

Hey Neil,

How would I do this, but rather than the first character being an 'A', it's any number?

Thanks!!

 

[Rick Rothstein]

Hey Neil,

How would I do this, but rather than the first character being an 'A', it's any number?

Using Neil's code layout and assuming you mean you still want to delete the first 3 characters for that condition, change the If..Then code line to this...

Sub test()
  With Sheets("Sheet1").Range("A1")
    If .Value Like "#*" Then .Value = Mid(.Value, 4)
  End With
End Sub