macro delete first three symbols

Paradi

New Member
Joined
Jul 6, 2018
Messages
7
51U Black Cloth Headliner
731 Burl Walnut Wood Trim
P17 Keyless-Go

<tbody>
</tbody>
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
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
18,030
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
First, select the cells you want to modify, then run this macro.
Code:
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
 
Last edited:
Upvote 0

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
81,453
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to the board.
How about
Code:
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
 
Upvote 0

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
18,030
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.
Code:
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
 
Upvote 0

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
81,453
Office Version
  1. 365
Platform
  1. Windows
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:)
 
Last edited:
Upvote 0

Paradi

New Member
Joined
Jul 6, 2018
Messages
7
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) ...
:(
 
Upvote 0

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
58,417
Office Version
  1. 365
Platform
  1. Windows
#VALUE ! error in those cells.
Code:
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.
Code:
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
 
Upvote 0

Paradi

New Member
Joined
Jul 6, 2018
Messages
7
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 :)
 
Last edited by a moderator:
Upvote 0

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
58,417
Office Version
  1. 365
Platform
  1. Windows
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. :biggrin:
 
Upvote 0

Paradi

New Member
Joined
Jul 6, 2018
Messages
7

<tbody>
</tbody>
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. :biggrin:

:) 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
 
Upvote 0

Forum statistics

Threads
1,186,217
Messages
5,956,611
Members
438,257
Latest member
Mark_365

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top