VBA & VB Script: retrieve 1st and Second level of string

Hello Experts,

I have one Excel sheet with the following information in column A
Organization
RAD.DIV.LO.RUD Management.Wiring Devices.WD EMEAS.BS
RAD.DIV.LO.RUD Management.Wiring Devices.WD EMEAS.BS
RAD.DIV.DIV Projects.Supply Chain.RUD Management

Open in new window


I need a VBA or a VB Script to keep level 2 and 3 of the string like this (each level is delimited by "."):
Organization
DIV.LO
DIV.LO
DIV.DIV Projects

Open in new window


Thank you in advance for your help.
LVL 1
LD16Asked:
Who is Participating?
 
Jeff DarlingDeveloper AnalystCommented:
I think you are asking how to use this function.

=FirstTwo(A1)

Open in new window

ee-2015030301.xlsm
0
 
Jeff DarlingDeveloper AnalystCommented:
Public Function FirstTwo(strIn As String) As String

Dim strTemp

strTemp = Split(strIn, ".")

If UBound(strTemp) = 0 Then
  FirstTwo = strTemp(0)
Else
  FirstTwo = strTemp(1) & "." & strTemp(2)
End If

End Function

Open in new window

0
 
LD16Author Commented:
Hello Jeff,
Thank you for this.
Is there a way to add input box or a variable to specify the column concerned by the extraction of level 2 and 3?
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
LD16Author Commented:
Got it, thank  you for this explanation.

Now the question is how can I call this new function to automatically generate the value through a loop with something like this


Sub split_data()

Dim c As String, FirstTwo As Variant

For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
c.Offset(, 1).Value = Application.WorksheetFunction.FirstTwo(c)
Next c
End Sub

Open in new window

0
 
aikimarkCommented:
@LD16

Since this is a VBA routine, you don't need to go through the worksheetfunction collection:
Sub split_data()

Dim c As String, FirstTwo As Variant

For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
    c.Offset(, 1).Value = FirstTwo(c)
Next c
End Sub

Open in new window

0
 
LD16Author Commented:
Hello Aikimark,

Thank you for this recommendation however I was not able to properly launch your revised code.
Additionnally how can I add this function in order use it in every excel file?

Thank you in advance for your feedback.
0
 
aikimarkCommented:
Where did you place the FirstTwo routine?
If not in the same module, it will need to be defined with the Public keyword.
0
 
LD16Author Commented:
I placed firsttwo in the same module as splitdata.
0
 
aikimarkCommented:
You need to use the value property of the range object iterator.
Sub split_data()

Dim c As String, FirstTwo As Variant

For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
    c.Offset(, 1).Value = FirstTwo(c.value)
Next c
End Sub

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.