Excel delete last 5 characters

[Robertson1995]

Hey guys, I need code that will delete the last 5 characters of every cell in Column H. Thanks in advance for the help.

 

[brettdj]

An array code is one option

Another way would be a simple IF test in a working column (manual or via VBA) that would then post the truncated strings back over column H

hth

Dave

Sub ArrayWrite()
    Dim rng1 As Range
    Dim X
    Dim lngRow As Long
    Set rng1 = Range([h1], Cells(Rows.Count, "H").End(xlUp))
    X = rng1
    For lngRow = 1 To UBound(X)
        If Len(X(lngRow, 1)) >= 5 Then X(lngRow, 1) = Right$(X(lngRow, 1), Len(X(lngRow, 1)) - 5)
    Next
    rng1 = X
End Sub

 

[CWatts]

A helper column with a formula is another option:

=LEFT(H1,LEN(H1)-5)

 

[hiker95]

Robertson1995,


Sample data before the macro:

Excel Workbook
	H	I	J
1	H54321	I	J
2	HHH54321	I	J
3	HH54321	I	J
4	H54321	I	J
5	HHHH54321	I	J
6	HHH54321	I	J
7	H	I	J
8	HH	I	J
9	HHH	I	J
10	HHHH	I	J
11	HHHHH	I	J
12	HHHHHH	I	J
13	H54321	I	J
14
Sheet1

After the macro:

Excel Workbook
	H	I	J
1	H	I	J
2	HHH	I	J
3	HH	I	J
4	H	I	J
5	HHHH	I	J
6	HHH	I	J
7	H	I	J
8	HH	I	J
9	HHH	I	J
10	HHHH	I	J
11	HHHHH	I	J
12	H	I	J
13	H	I	J
14
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub DeleteLast5()
' hiker95, 04/19/2011
' http://www.mrexcel.com/forum/showthread.php?t=544815
Dim LR As Long, LC As Long
Application.ScreenUpdating = False
LR = Cells(Rows.Count, "H").End(xlUp).Row
LC = Cells.Find("*", , xlValues, xlWhole, xlByColumns, xlPrevious, False).Column
Cells(1, LC + 2).Formula = "=IF(LEN(H1)<6,H1,LEFT(H1,LEN(H1)-5))"
Cells(1, LC + 2).AutoFill Destination:=Range(Cells(1, LC + 2), Cells(LR, LC + 2))
Columns("H:H").Value = Columns(LC + 2).Value
Columns(LC + 2).ClearContents
Application.ScreenUpdating = True
End Sub

Then run the DeleteLast5 macro.