LEFT Function to retain first 10 characters of cells with VBA

[sllaksvb]

Hi all,

I'm having trouble with creating the code for the left function in VBA.
What I am trying to achieve is to retain the first 10 characters of each cell, without any spaces, and all in upper case.

I've attached a sample doc of how it's supposed to look, before in column A and after in column B. However, I do not want it to be placed into a new column, but instead replacing the existing values in column A.

Any help would be greatly appreciated! Thank you!

2116421164

[JKwan]

give this a try:

Option Explicit
Sub Main()
Dim WS As Worksheet
Dim LastRow As Long
Dim lRow As Long
Dim Temp As String

Set WS = Worksheets("Sheet1")
LastRow = FindLastRow(WS, "A")
For lRow = 2 To LastRow
With WS
Temp = Left(UCase(Replace(.Cells(lRow, "A"), " ", "")), 10)
.Cells(lRow, "A") = Temp
End With
Next lRow
End Sub
Function FindLastRow(ByVal WS As Worksheet, ColumnLetter As String) As Long
FindLastRow = WS.Range(ColumnLetter & Rows.Count).End(xlUp).Row
End Function

[Paul_Hossler]

You might have to change the way the Range is defined




Option Explicit

Sub FixColumn()
Dim c As Range
For Each c In Range(Range("A2"), Range("A2").End(xlDown)).Cells
c.Value = Left(UCase(Replace(c.Value, " ", vbNullString)), 10)
Next
End Sub

[snb]

Simple as this:

Sub M_snb()
[A2:A2000] = [if(A2:A2000="","",upper(left(substitute(A2:A2000," ",""),10)))]
End Sub

[sllaksvb]

JKwan, Paul, and snb,

Thank you for your help! Really appreciate it. Went ahead using Paul's code as I could understand the logic and apply it in my macro.