Tool That Delete A Specified Number Of Characters From The Beginning Or End Of Cell Data

[miketurn]

Hello Everyone,

I am not sure if something like this is possible to easily achieve, but I am looking for a macro that can do the following...

Manually
1.) Select cells with data that you want to remove a specified numbers of spaces from the beginning or end of.
2.) Click on tool button that has the following macro assigned to it.

Here is where macro will start...
3.) A dialog box pops up with the phrase "Enter Number Of Spaces To Remove From Beginning" and it has a blank field to type a number into.
If you type a number into the field and hit enter it will remove that many spaces from the beginning of the selected cells text.
If you leave field blank and hit enter it does not delete any spaces from beginning of the selected cells text.
4.) Next a dialog box pops up with the phrase "Enter Number Of Spaces To Remove From End" and it has a blank field to type a number into.
If you type a number into the field and hit enter it will remove that many spaces from the end of the selected cells text.
If you leave field blank and hit enter it does not delete any spaces from the end of the selected Cells text.

So this one tool basically will allow you to delete a specified number of characters (letters or numbers) from the beginning or ends of all the cells you select that contain text.
(It might be nice to create the macro with the ability for someone to be able to remove the "beginning" or "end" coding if they just want a tool to do one specifically, instead of having them both in one tool, if it can easily be done.)

It is not a tool that will be used constantly, but I myself would find it to be a useful tool in the toolbox, and I have seen many others ask for the ability to achieve similar results, but I feel this one created how I explain above, covers all the bases and can be helpful to many (hopefully).

Thank You again for anyone who reads this and is willing to give it a shot.
Any questions please let me know, and I will try to explain better.

 

[Michael M]

That's quite true Michael, but I thought this meant that the door was left slightly ajar.

Touche`, Peter...

 

[Tetra201]

@miketurn:

See if this works for you:

Sub Tool()
    For Each s In Array("Beginning", "End")
        n = Application.InputBox("Enter Number Of Characters To Remove From " & s, Default:=0, Type:=1)
        If n <> 0 Then
            n = Int(Abs(n))
            Application.ScreenUpdating = False
            For Each cll In Selection
                If n > Len(cll.Value) Then cll.Value = "" Else cll.Value = Mid(cll.Value, 1 - n * (s = "Beginning"), Len(cll.Value) - n)
            Next cll
            Application.ScreenUpdating = True
        End If
    Next s
End Sub

 

[miketurn]

WOW these are great tools, thank you guys for providing them.

@Michael M
Thank You, for your response, I did run into similar things that Peter_SSs mentioned to you

@Peter_SSs
Thank You for your response, it is funny when I saw the Function offered by Tetra201 I noticed the 1 and -1 and thought about mentioning that. Like how you offered that in the macro that you provided to make it one dialog box, very creative.
Thank You again

@Tetra201
Thank You again for the Function you provided as well as the new macro which works great, I like how you added "0" to the fields which prevents the error, very nice.

Again Thank You all these tools are very helpful, and I hope others will make use of them as well.

 

[Tetra201]

You are welcome.

 

[Peter_SSs]

@Peter_SSs
Thank You for your response, it is funny when I saw the Function offered by Tetra201 I noticed the 1 and -1 and thought about mentioning that. Like how you offered that in the macro that you provided to make it one dialog box, very creative.
Thank You again

You are welcome from me too.

I was reflecting that perhaps the negative/positive options may not be that user-friendly. Another option where the user chooses left/right would be as below.
This time the user enters "L2" or "R10" etc to indicate which side to remove and how many characters (lower case l/r should also work).
I haven't tested greatly but you may consider giving this a go too.

Sub RemoveCharacters_v2()
  Dim s As String
  Dim num As Variant
  
  s = Application.InputBox(Prompt:="Enter number of characters to remove." & vbLf & _
      "Use 'L' Followed by a number to remove from the left." & vbLf & "Use 'R' Followed by a number to remove from the right.")
  num = Mid(s, 2)
  If IsNumeric(num) Then
    num = CLng(Abs(num))
    With Selection
      Select Case True
        Case UCase(Left(s, 1)) = "L"
          .Value = Evaluate(Replace(Replace("if(len(#),replace(#,1,^,""""),"""")", "#", .Address), "^", num))
        Case UCase(Left(s, 1)) = "R"
          .Value = Evaluate(Replace(Replace("if(len(#)<^,"""",left(#,len(#)-^))", "#", .Address), "^", num))
      End Select
    End With
  End If
End Sub

 

[miketurn]

@Peter_SSs
Thank You for your added update, I don't necessarily mind the positive/negative but I can understand your reasoning for this, thank you for providing this possibility. I tested this updated macro out quite a few times and I didn't come across any issues either.

Thank You guys again for providing all of these options, now myself and other future users can take our pick of the method we prefer.