Results 1 to 4 of 4

Thread: VBA code to split first three letters

  1. #1

    VBA code to split first three letters

    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi Guys!

    I am looking for a very simple thing. A VBA code for splitting first three letters of a column to the column before (offset -1). For ex. if K column contains "SEKWPRTY6" then should be "SEK" in J column and "WPRTY6" should stay in K column still.

    My 'Dim's are usually going like this

    Dim cell As Range
    Dim bottomK As Integer
    bottomK = Range("k" & Rows.Count).End(xlUp).Row
    Dim rng As Range
    Set rng = Range("K1:K" & bottomK)
    For Each cell In rng
    so I would be happy if you try to use same 'Dim's.

    Thanks a lot!
    Last edited by Bob Phillips; 2012-08-06 at 09:13 AM. Reason: Added code tags

  2. #2
    It worked for me like this. What if I would like to split it to next column(not the column before like in this example). For ex. "L"

    Sub SplitTest()
        Dim rng As Range
        For Each rng In Range("k1", Range("k" & Rows.Count).End(xlUp))
            rng(, 0).Value = Right$(rng.Value, 1)
            rng.Value = Mid$(rng.Value, 2)
    End Sub
    Last edited by Bob Phillips; 2012-08-06 at 09:11 AM. Reason: Added code tags

  3. #3
    This is based on what you said you wanted in your first post. take first three from left.

    Sub Split3()
    Dim c As Range
    For Each c In Range("k1", Range("k" & Rows.Count).End(xlUp))
      c.Offset(, 1) = Left(c, 3)
      c = Left(c, Len(c) - 1)
    Next c
    End Sub

  4. #4
    if you liked the code in your second post just change the 0 to a 2 in

    rng(, 0).Value = Right$(rng.Value, 1)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts