macro delete first three symbols

[Paradi]

[TABLE="width: 500"]
<tbody>[TR]
[TD]51U Black Cloth Headliner
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]731 Burl Walnut Wood Trim
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P17 Keyless-Go
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Hi,

I couldn't find good macros to delete firs three symbols from cell (51U, 731, ).. its not always the same symbols... I was trying a lot off different macros and always something is wrong any idea?



I kwon the formula (=right(a1,len.....) and its work, but I need macros because I partly remove the cells and later I all these cells separate using commas.


Please help me create a macro to remove the 3 first symbols

 

[JoeMo]

First, select the cells you want to modify, then run this macro.

Sub LoseFirstThree()
'First, select the cells you want to modify, then run this macro
Dim c As Range
Application.ScreenUpdating = False
For Each c In Selection
    If Len(c.Value) > 2 Then c.Value = Trim(Right(c.Value, Len(c.Value) - 3))
Next c
Application.ScreenUpdating = True
End Sub

 

[Fluff]

Hi & welcome to the board.
How about

Sub RemoveString()
   With Range("A2", Range("A" & Rows.Count).End(xlUp))
      .Value = Evaluate(Replace("If({1},right(@,len(@)-4),"""")", "@", .Address))
   End With
End Sub

This assumes your values are in col A

 

[JoeMo]

Fluff, I like your clever use of the Evaluate method. In this instance, if it's possible that some of the cells have strings of fewer than 4 characters, this modification will avoid creating a #VALUE ! error in those cells.

Sub RemoveString()
   With Range("A2", Range("A" & Rows.Count).End(xlUp))
      .Value = Evaluate(Replace("If(len(@)>3,right(@,len(@)-4),@)", "@", .Address))
   End With
End Sub

 

[Fluff]

Fluff, I like your clever use of the Evaluate method.

Thanks for that, I learnt it from Rick Rothstein.
I also like your idea of checking that there are more then 3 characters. Far safer than assuming it

 

[Paradi]

Hi

Thank You for you answer but it steel not the best because its not delete code start for letter (like P17) or not delete last letter for code wth letter (like 51U) ...

 

[Peter_SSs]

#VALUE ! error in those cells.

Sub RemoveString()
   With Range("A2", Range("A" & Rows.Count).End(xlUp))
      .Value = Evaluate(Replace("If(len(@)>3,right(@,len(@)-4),@)", "@", .Address))
   End With
End Sub

Some further comments
1. This code of course actually removes 4 characters, not 3 as requested. Whilst the few samples provided all have a space character 4th and we presume that is to be removed too, I think it safer to remove 3 and trim.
2. If there are any blank cells in the range, this code replaces those with a 0.
3. From the OP's comments in post 6, I'm wondering if some of the original data may actually have leading spaces as well.

So, this is my suggestion, still assuming data is in column A from row 2 down.

Sub Delete3()
  With Range("A2", Range("A" & Rows.Count).End(xlUp))
    .Value = Evaluate(Replace("if(#="""","""",trim(replace(trim(#),1,3,"""")))", "#", .Address))
  End With
End Sub

 

[Paradi]

Guys You are the best!!!!!
Thank You very much

can you tell me where I can find the best information about the macro.
I'm learning excel from the internet and I always get ready macro but I would like to learn how to create macro myself (like You )


Thank You

 

[Peter_SSs]

Guys You are the best!!!!!
Thank You very much

Glad you got a successful outcome. Thanks for letting us know.

can you tell me where I can find the best information about the macro.
I'm learning excel from the internet and I always get ready macro but I would like to learn how to create macro myself (like You )

I have done nearly all of my macro learning right here in this forum.

 

[Paradi]

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Glad you got a successful outcome. Thanks for letting us know.


I have done nearly all of my macro learning right here in this forum.

Thank You for answer, I will be a permanent guest here....

I have another question:
When I separate comma my text I don't have a space after the comma. I know than I could use "trim(substitute...." option by macros would be much better
Maybe You have idea

I was using these one:

Sub ChangeRange()
'Updateby20140310
Dim rng As Range
Dim InputRng As Range, OutRng As Range
xTitleId = "KutoolsforExcel"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8)
Set OutRng = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8)
outStr = ""
For Each rng In InputRng
If outStr = "" Then
outStr = rng.Value
Else
outStr = outStr & "," & rng.Value
End If
Next
OutRng.Value = outStr
End Sub