Extracting a string out of another string

[Steven]

In the following example:

Responsibility: Name1 / Name2 / Name3

How would I extract out Name1 in a macro and put it an an adjacent cell?

Thank you,

Steven

 

[cathellisuk]

In the following example:

Responsibility: Name1 / Name2 / Name3

How would I extract out Name1 in a macro and put it an an adjacent cell?

Thank you,

Steven

How about this

Sub extract_first_name()
' where the cell A1 contains "name1/name2/name3"
Dim sResponsibility As String
Dim sFirstName As String
sResponsibility = Cells(1, 1)
sFirstName = Left(sResponsibility, InStr(1, sResponsibility, "/") - 1)
Cells(1, 2) = sFirstName
End Sub

Or if you have a whole column of cells containing multiple names
then ....
Sub extract_first_name_colum()
Dim sResponsibility As String
Dim sFirstName As String
Dim myRow As Integer
myRow = 1

Do Until Cells(myRow, 1) = ""
sResponsibility = Cells(myRow, 1)
sFirstName = Left(sResponsibility, InStr(1, sResponsibility, "/") -
1)
Cells(myRow, 2) = sFirstName
myRow = myRow + 1
Loop
End Sub

Cath

 

[Rick Rothstein \(MVP - VB\)]

Without any other instruction from you, I'll simply assume that text is in
the ActiveCell. Put this code in your macro...

With ActiveCell
.Offset(0, 1).Value = Trim(Split(Split(.Value, ":")(1), "/")(0))
End With

Rick

 

[Steven]

That is very nice. Thank you.