How remove the Last 5 character throu VBA

AT BABU

Board Regular
Joined
Oct 12, 2018
Messages
54
Office Version
  1. 2016
Platform
  1. Windows
Hi Folks

I need small macros removeing Last 5 character of the Word

In Exp: AT_1833 I need AT only. Removring _1833
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Code:
var2=LEFT( var1, LEN(var1) - 5 )

where var1 is the input variable and var2 is the result.
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Thank You For your your reply. I little confused for this code

Thank you Once again !!
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top