VBA code to split first three letters

Flashbond

New member
Joined
Aug 4, 2012
Messages
2
Reaction score
0
Points
0
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

Code:
Dim cell As Range
[/FONT][FONT=Verdana]Dim bottomK As Integer[/FONT]
[FONT=Verdana]bottomK = Range("k" & Rows.Count).End(xlUp).Row[/FONT]
[FONT=Verdana]Dim rng As Range[/FONT]
[FONT=Verdana]Set rng = Range("K1:K" & bottomK)[/FONT]
[FONT=Verdana]For Each cell In rng[/FONT]

so I would be happy if you try to use same 'Dim's.

Thanks a lot!
 
Last edited by a moderator:

Flashbond

New member
Joined
Aug 4, 2012
Messages
2
Reaction score
0
Points
0
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"

Code:
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)
    Next
End Sub
 
Last edited by a moderator:

tommyt61

New member
Joined
Mar 26, 2011
Messages
153
Reaction score
0
Points
0
This is based on what you said you wanted in your first post. take first three from left.




Code:
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
 

tommyt61

New member
Joined
Mar 26, 2011
Messages
153
Reaction score
0
Points
0
if you liked the code in your second post just change the 0 to a 2 in

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