separate two strings

[Max Bialystock]

A cell contains an author's name and a book title separated by a colon.



Charles Dickens : Great Expectations



I need to get "Charles Dickens" into sAuthor

and "Great Expectations" into sTitle.



In this case there is always a space before and after the colon.

 

[Guest]

Perhaps this will help some:

Sub test()
Dim lngTemp As Long
Dim sAuthor As String
Dim sTitle As String

strLiterature = Range("A1").Value
lngTemp = InStr(1, strLiterature, ":", vbTextCompare)
If lngTemp > 0 Then
sAuthor = Trim$(Left$(strLiterature, lngTemp - 1))
sTitle = Trim$(Mid$(strLiterature, lngTemp + 1, Len(strLiterature)))
End If

End Sub

 

[Gary Keramidas]

one way, assuming data is in column A on sheet1 and you want the data in columns
C & D
Sub split_names()
Dim ws As Worksheet
Dim sName As Variant
Dim i As Long
Dim lastrow As Long
Dim sAuthor As Long, sTitle As Long
sAuthor = 3 ' column C
sTitle = 4 ' column D
Set ws = Worksheets("sheet1")
lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To lastrow
sName = Split(ws.Range("A" & i), ":")
ws.Cells(i, sAuthor).Value = sName(0)
ws.Cells(i, sTitle).Value = sName(1)
Next

End Sub

 

[Gary Keramidas]

and if you wanted to trim the spaces
Option Explicit

Sub split_names()
Dim ws As Worksheet
Dim sName As Variant
Dim i As Long
Dim lastrow As Long
Dim sAuthor As Long, sTitle As Long
sAuthor = 3 ' column C
sTitle = 4 ' column D
Set ws = Worksheets("sheet1")
lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To lastrow
sName = Split(ws.Range("A" & i), ":")
ws.Cells(i, sAuthor).Value = Trim(sName(0))
ws.Cells(i, sTitle).Value = Trim(sName(1))
Next

End Sub

 

[Rick Rothstein \(MVP - VB\)]

A cell contains an author's name and a book title separated by a colon.

Charles Dickens : Great Expectations

I need to get "Charles Dickens" into sAuthor

and "Great Expectations" into sTitle.

In this case there is always a space before and after the colon.

CellValue = "Charles Dickens : Great Expectations"
sAuthor = Split(CellValue, " : ")(0)
sTitle = Split(CellValue, " : ")(1)

Rick

 

[Guest]

initial = cells("A1")
mid_index = Instr(intitial, " : ")
name = left(intial,mid_index-1)
title = mid(initial,mid_index+3)

 

[Max Bialystock]

Gary,

Thanks for your help.

Max

and if you wanted to trim the spaces
Option Explicit

Sub split_names()
Dim ws As Worksheet
Dim sName As Variant
Dim i As Long
Dim lastrow As Long
Dim sAuthor As Long, sTitle As Long
sAuthor = 3 ' column C
sTitle = 4 ' column D
Set ws = Worksheets("sheet1")
lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To lastrow
sName = Split(ws.Range("A" & i), ":")
ws.Cells(i, sAuthor).Value = Trim(sName(0))
ws.Cells(i, sTitle).Value = Trim(sName(1))
Next

End Sub

 

[Guest]

What about my help?

Gary,

Thanks for your help.

Max