VBA Left

Livin404

Active Member
Joined
Jan 7, 2019
Messages
392
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Greetings I have USA / 0002 in column H starting in row 5. The only thing I want up & down the Column is the first three letters. In the example I want to be left with USA. The there are nearly 300 different letter codes.

The code I'm wanting to use is, but I'm off the mark: Thank you!
VBA Code:
Sub UseLeft()
Dim LResult As String
 With Range("H5", Range("H" & Rows.Count).End(xlUp))
LResult = Left(text, 3)
 End With
End Sub
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

silentwolf

Well-known Member
Joined
May 14, 2008
Messages
1,164
Office Version
  1. 2016
Hi,
so you like to get only the first three letters from a column? Where do you like to have the result? In the same column?
Use a function instead to return the value.

VBA Code:
Function ReturnFirstThreeLetters(ByVal strText As String) As String
    ReturnFirstThreeLetters = Left(strText, 3)
End Function
 

davesexcel

Well-known Member
Joined
Feb 26, 2006
Messages
1,089
Loop through and just keep the 1st 3
VBA Code:
Sub UseLeft()
    Dim sh As Worksheet
    Dim c As Range
    Set sh = ActiveSheet
    With sh
        For Each c In .Range("H5:H" & .Cells(.Rows.Count, "H").End(xlUp).Row)
            c = Left(c, 3)
        Next
    End With
End Sub
 

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,206
Office Version
  1. 2016
Platform
  1. Windows
VBA Code:
Sub UseLeft()
Dim r$: r = Range("H5", Range("H" & Rows.Count).End(xlUp)).Address
Range(r) = Evaluate("=Left(" & r & ", 3)")
End Sub
 
Solution

Livin404

Active Member
Joined
Jan 7, 2019
Messages
392
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hi,
so you like to get only the first three letters from a column? Where do you like to have the result? In the same column?
Use a function instead to return the value.

VBA Code:
Function ReturnFirstThreeLetters(ByVal strText As String) As String
    ReturnFirstThreeLetters = Left(strText, 3)
End Function
yes the same column I want all other data in those cells deleted leaving the first three characters in the same column. I use exclusively Sub if I can. I get muddled with Functions especially when I'm asked to run the macro. Thank you
 

Livin404

Active Member
Joined
Jan 7, 2019
Messages
392
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
VBA Code:
Sub UseLeft()
Dim r$: r = Range("H5", Range("H" & Rows.Count).End(xlUp)).Address
Range(r) = Evaluate("=Left(" & r & ", 3)")
End Sub
Thank you exactly what I was looking for.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,442
Messages
5,624,800
Members
416,054
Latest member
Ariel2219

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
Top