Right and left VBA code

JorgenKjer

Board Regular
Joined
Aug 1, 2016
Messages
60
Office Version
  1. 2013
Platform
  1. Windows
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​
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Bo_Ry

Board Regular
Joined
Oct 27, 2018
Messages
142
Office Version
  1. 365
Platform
  1. Windows
Please try


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

Board Regular
Joined
Aug 1, 2016
Messages
60
Office Version
  1. 2013
Platform
  1. Windows
Hi Bo_Ry
I may be doing something wrong but I get the result below when I run the code

ABC
MG5667-56-03-03-02MG5667MG5667
MG5617-58-03-03-02MG5667MG5667
MG5605-56-03-03-01MG5667MG5667
MG5675-58-03-03-01MG5667MG5667
MG5676-58-03-03-01MG5667MG5667
MG5659-56-03-03-02MG5667MG5667
MG5667MG5667
MG5667MG5667
MG5667MG5667
MG5667MG5667
MG5667MG5667
MG5667MG5667
MG5667MG5667
MG5667MG5667
MG5667MG5667
MG5667MG5667
MG5667MG5667
MG5667MG5667
MG5667MG5667
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,253
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Oct 27, 2018
Messages
142
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

2 more ways

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,253
Office Version
  1. 365
Platform
  1. Windows
Another possible option, using Bo_Ry's original code
VBA 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

Board Regular
Joined
Aug 1, 2016
Messages
60
Office Version
  1. 2013
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,006
Messages
5,622,138
Members
415,880
Latest member
Bruce0203

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top