asked on

Excel VBA: remove # number of characters from the last to the beginning

Hello experts,

I have the following procedure in order to remove last character.
I would like to add the following requirements.

Sub Remove_Last_Char()
Dim c As Range
Application.ScreenUpdating = False
For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
If c.Value <> "" Then c.Value = Left(c, Len(c) - 1)
Next c
Application.ScreenUpdating = True
End Sub

Open in new window

1-Input box “Please enter the column letter in which do you want to remove last characters”
2-Input box: "How many characters do you want to removed from the last to the beginning?"
Report in next available column the modification performed by the procedure.

If you have questions, please contact me.

Robberbaron (robr)

Sub Remove_Last_CharFromColA()
    Dim c As Range
    Application.ScreenUpdating = False
    For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
        If c.Value <> "" Then c.Value = Left(c, Len(c) - 1)
    Next c
    Application.ScreenUpdating = True
End Sub

Sub SelectCol_Count()
    Dim colid As String, charcount As String
    colid = InputBox("Please enter the column letter in which do you want to remove last characters", "Select Action Column", "A")
    Do
        charcount = InputBox("How many characters do you want to removed from the last to the beginning?", "Characters to delete", 1)
    Loop While Not IsNumeric(charcount)
    
    Remove_EndChars_FromCol colid, Val(charcount)
    
End Sub

Sub Remove_EndChars_FromCol(colid As String, Optional numChars As Integer = 1, Optional outputCol As String = "")
    Dim c As Range, startCell As Range
    Dim newVal As Variant
    
    Set startCell = Range(colid & "1")
    Set endCell = Range(startCell, startCell.Offset(startCell.Worksheet.Rows.Count - startCell.Row, 0).End(xlUp))
    Application.ScreenUpdating = False
    For Each c In Range(startCell, endCell)
        If c.Value <> "" Then newVal = Left(c, Len(c) - numChars)
        If outputCol = "" Then
            'use next
            c.Offset(0, 1) = newVal
         Else
            Set outputcell = Range(outputCol & c.Row)
        End If
    Next c
    Application.ScreenUpdating = True
End Sub

Open in new window

run the SelectCol_Count macro.

Luis Diaz

ASKER

Thank you for this proposal.
I would like to have a single Sub procedure as I will use on a regular basis from my Personal.xlsb. Is it possible to simplify?

Robberbaron (robr)

No. It is much better to have the calling routine and the action function. Just put both in personal
Xlsm

ASKER CERTIFIED SOLUTION

Bill Prew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Luis Diaz

ASKER

Tested and it works. Thank you very much for your help!