Trimming left

[chandler1983]

Hi Everyone

I think this will be a simple one, but I cannot find exactly what I need.

I need to trim values (all text) in column H to 8 characters. I can't seem to do this in VBA.
I tried on the front end but I can't use this as the clear we have wipes it out.

I just need it to keep the LEFT 8 and drop anything left.

 

[Marcílio_Lobão]

chandler1983, Good afternoon.

To use a TEXT to COLUMN is an option for you?

 

[mumps]

Tryt:

Sub TrimVals()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim rng As Range
    For Each rng In Range("H2:H" & LastRow)
        rng = Left(rng, 8)
    Next rng
    Application.ScreenUpdating = True
End Sub

 

[chandler1983]

you know what yeah, I can make that work. thanks for the idea!

 

[Marcílio_Lobão]

chandler1983,

Glad to have helped you.

Thanks for the feedback.

 

[Rick Rothstein]

Tryt:

Sub TrimVals()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim rng As Range
    For Each rng In Range("H2:H" & LastRow)
        rng = Left(rng, 8)
    Next rng
    Application.ScreenUpdating = True
End Sub

I know the OP has decided to use the manual version of Text To Columns, but it can also be used to create a one-liner macro...

[table="width: 500"]
[tr]
	[td]Sub GetLeftEight()
  Range("H2", Cells(Rows.Count, "H").End(xlUp)).TextToColumns , xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(8, 9))
End Sub[/td]
[/tr]
[/table]

 

[mumps]

Thanks, Rick. I was looking for a one-liner but I couldn't figure it out. Would you mind explaining how

FieldInfo:=Array(Array(0, 1), Array(8, 9))

works? Many thanks.

 

[Rick Rothstein]

Thanks, Rick. I was looking for a one-liner but I couldn't figure it out. Would you mind explaining how

FieldInfo:=Array(Array(0, 1), Array(8, 9))

works? Many thanks.

The first number in the array is the starting position for the field and the second number is the equivalent value for the option for that field (1 is General, 9 is Skip Column... the other options are for text and date order); however, I never work those numbers out for myself, rather, what I do is turn on the macro recorder and perform the action manually and I let it generate the FieldInfo for me.

 

[mumps]

Much appreciated.