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.

 

[mole999]

apart from using TRIM functions, which get rid of all the excess spaces, have a look for ASAP Utilities, and play with those available toolbar functions, you might decide you don't need anything else

 

[miketurn]

@mole999
Thank You for your response, I was hoping for more of a macro for something like this, as opposed to having to install an addin for it.
When searching before I posted this questions, that is what others were using, the "trim" function, but I don't know enough about writing macros at the moment to create what I am looking for. Thank You again.

If anyone has any ideas please let me know.

 

[Michael M]

So do you actually want to TRIM the spaces from the text...I don't see a reason to leave any behind at either end ???
Using the TRIM function below, will remove ALL excess spaces from the selected cell

Sub MM1()
Selection = Trim(Selection)
End Sub

If you want to do more than one cell you would need to create a loop in the macro

Sub MM1()
For Each c In Selection
    c.Value = Trim(c.Value)
Next c
End Sub

 

[Tetra201]

... 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...

Give a try to the following UDF (user-defined function). Usage example:
A1 ="Text"
=Shorten(A1,1) returns "Tex"
=Shorten(A1,-1) returns "ext"
=Shorten(A1,100) returns ""

Function Shorten(s As String, n As Long) As String
    If Abs(n) > Len(s) Then Shorten = "": Exit Function
    If n > 0 Then Shorten = Mid(s, 1, Len(s) - n) Else Shorten = Mid(s, 1 - n, Len(s) + n)
End Function

 

[miketurn]

@Michael
Ignore my response about TRIMS that is only for apparently only for removing spaces.
Please see my example below.

@Tetra201
Thank You for your response, what you have provided is along the same lines as what I am looking to do, but by any chance do you or anyone else know if this can be done in a macro, I have not seen any kind of code that allows for this?


I am looking to be able to remove actual characters (letters/numbers/etc) from the beginning/end of cell data.
EXAMPLE:
If you had the following three cells selected and typed a number 2 into the input box that is linked to remove from the BEGINNING you would get.....
APPLE_________PLE
ORANGE_______ANGE
BANANA_______NANA
If you had the following three cells selected and typed a number 2 into the input box that is linked to remove from the END you would get.....
APPLE_________APP
ORANGE_______ORAN
BANANA_______BANA

Even if they have to be two separate tools as opposed to my request above that requests that they are one combined tool.

Thank You

 

[Michael M]

Ok, maybe like this
I know this is what you asked for, but you could probably shorten it to ask the question..."Do you want START or END of string removed ???"

Sub MM1()
Dim c As Range
ans = InputBox("Enter number of letters to remove from START of STRING ! ")
For Each c In Selection
    c.Value = Mid(c, ans + 1, 99)
Next c
ans = InputBox("Enter number of letters to remove from END of STRING ! ")
For Each c In Selection
    c.Value = Left(c, Len(c) - ans)
Next c
End Sub

 

[Peter_SSs]

Not sure if this approach might be of interest? Only one input box appears. Enter a negative number to remove characters from the left or a positive number to remove from the right.
If you want to remove from both ends then while the range is still selected, run the code again.

Sub RemoveCharacters()
  Dim Num As Long
  
  Num = Application.InputBox(Prompt:="Enter number of characters to remove." & vbLf _
                                    & "Use a negative number to remove from left." & vbLf _
                                    & "Use a positive number to remove from the right.", Type:=1)
  If Num <> 0 Then
    With Selection
      If Num > 0 Then
        .Value = Evaluate(Replace(Replace("if(len(#)<^,"""",left(#,len(#)-^))", "#", .Address), "^", Num))
      Else
        .Value = Evaluate(Replace(Replace("if(len(#),replace(#,1,^,""""),"""")", "#", .Address), "^", -Num))
      End If
    End With
  End If
End Sub

Michael, a couple of comments re your code.
If the user enters a number to delete from the right and that number is greater than the length of any cell's text then the code will error.
It will also error if the user enters text in the input box or presses Cancel at the input box.
Note that for the mid() function in vba, the third argument is optional. If you leave it out the whole rest of the string is returned. That would be safer that using an arbitrary value like 99.

 

[Michael M]

Thanks for the comments Peter....I always seem to assume, incorrectly of course, that the OP will think like I do !!!
AND
As always I like you clean & efficient code ...ray:


That aside....the OP did ask for 2 InputBoxes....

 

[Peter_SSs]

That aside....the OP did ask for 2 InputBoxes....

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

(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.)