Right and left VBA code

[JorgenKjer]

Hi

Is there anybody that can help with a VBA code?

I have a range of values in column A

In column B I want the first 6 characters from the left

In column C, I want 11 characters from the right

The number of rows in column A may change

Thank you in advance for your help

Yours sincerely

Jørgen Kjer

A​	B​	C​
MG5667-56-03-03-02​	MG5667​	56-03-03-02​
MG5617-58-03-03-02​	MG5617​	58-03-03-02​
MG5605-56-03-03-01​	MG5605​	56-03-03-01​
MG5675-58-03-03-01​	MG5675​	58-03-03-01​
MG5676-58-03-03-01​	MG5676​	58-03-03-01​
MG5659-56-03-03-02​	MG5659​	56-03-03-02​

 

[Bo_Ry]

Please try

Sub sp()
Dim r As Range
Set r = Range("A2:A20")
r.Offset(, 1).Resize(, 2) = Evaluate("MID(" & r.Address & ",{1,8},{6,11})")
End Sub

 

[JorgenKjer]

Hi Bo_Ry
I may be doing something wrong but I get the result below when I run the code

A	B	C
MG5667-56-03-03-02	MG5667	MG5667
MG5617-58-03-03-02	MG5667	MG5667
MG5605-56-03-03-01	MG5667	MG5667
MG5675-58-03-03-01	MG5667	MG5667
MG5676-58-03-03-01	MG5667	MG5667
MG5659-56-03-03-02	MG5667	MG5667
	MG5667	MG5667
	MG5667	MG5667
	MG5667	MG5667
	MG5667	MG5667
	MG5667	MG5667
	MG5667	MG5667
	MG5667	MG5667
	MG5667	MG5667
	MG5667	MG5667
	MG5667	MG5667
	MG5667	MG5667
	MG5667	MG5667
	MG5667	MG5667

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Bo_Ry]

2 more ways

Sub sp2()
Dim r As Range
For Each r In Range("A1", Cells(Rows.Count, 1))
    r(1, 2).Resize(, 2) = Split(r, "-", 2)
Next
End Sub


Sub sp3()
Application.DisplayAlerts = False
    Range("A1", Cells(Rows.Count, 1)).TextToColumns [B1], xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(6, 9), Array(7, 1))
Application.DisplayAlerts = True
End Sub

 

[Fluff]

Another possible option, using Bo_Ry's original code

Sub sp()
Dim r As Range
Set r = Range("A2:A20")
r.Offset(, 1).Resize(, 2) = Evaluate("if({1},MID(" & r.Address & ",{1,8},{6,11}))")
End Sub

 

[JorgenKjer]

Hi Bo_Ry
Both code sp2 and sp3 work perfectly. sp3 run much faster though.
Many thanks for the help.
Yours sincerely
Jørgen Kjer