How remove the Last 5 character throu VBA

AT BABU

Board Regular
Joined
Oct 12, 2018
Messages
53
Office Version
2016
Platform
Windows
Hi Folks

I need small macros removeing Last 5 character of the Word

In Exp: AT_1833 I need AT only. Removring _1833
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,342
Code:
var2=LEFT( var1, LEN(var1) - 5 )
where var1 is the input variable and var2 is the result.
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,342
That is VBA code
But you need to explain more than just "removing the last 5 character of the word"

For example where does the data start?
Where does it end?
Is it a UDF you want or a full macro?
 

AT BABU

Board Regular
Joined
Oct 12, 2018
Messages
53
Office Version
2016
Platform
Windows
EX:

AT_1833
BT_1693
ST_1733
I need remove the "_1833","_1693"and "_1733" these number Removeing. I want AT, BT and ST only I want full macro
 

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,049
Office Version
365, 2010
If your initial data is in column A with A1 being a header and you want the result in column B (starting in B2), try:

Code:
Sub RemoveLast5()
Dim lr As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
For i = 2 To lr
 Cells(i, 2) = Left(Cells(i, 1), Len(Cells(i, 1)) - 5)
Next i
End Sub
 

AT BABU

Board Regular
Joined
Oct 12, 2018
Messages
53
Office Version
2016
Platform
Windows
Thank You For your your reply. I little confused for this code

Thank you Once again !!
 

AT BABU

Board Regular
Joined
Oct 12, 2018
Messages
53
Office Version
2016
Platform
Windows
Thank You For your your reply.
 

jolivanes

Well-known Member
Joined
Sep 5, 2004
Messages
1,533
Office Version
2013, 2007
Platform
Windows
Both not dependent on amount of digits before/after underscore.
Code:
Sub Maybe_A()
    With Range("B2:B" & Cells(Rows.Count, 1).End(xlUp).Row)
        .Formula = "=LEFT(RC[-1],FIND(""_"",RC[-1])-1)"
        .Value = .Value
    End With
End Sub
Code:
Sub Maybe_B()
Dim i As Long
    For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
        Cells(i, 2).Value = Trim(Split(Cells(i, 1), "_")(0))
    Next i
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,100,187
Messages
5,473,023
Members
406,843
Latest member
David_Welland

This Week's Hot Topics

Top