Splitting text in a cell

[TheRook]

I currently have cells populated with text and am wanting to split the text
from within the cell into two.

Example:

a1: contains 'SHEFFIELD UNITED:BLADES'
a2: contains 'SHEFFIELD WEDNESDAY:OWLS'
I am wanting to split the text for that every thing to the left of ':' is in
on cell and every thing that is to the right of ':' to be in another cell.

Can this be done?

Cheers

 

[Gary''s Student]

Use Text to Columns:

1. select the column
2. pull-down Data > Text to Columns then select delimited then select the
colon

 

[Norman Jones]

Hi TheRook,

If you require a VBA solution, try something like:

'=============>>
Public Sub Tester()
Dim SH As Worksheet
Dim rng As Range
Dim rCell As Range
Dim arr As Variant

Set SH = ActiveSheet

With SH
Set rng = .Range("A1", .Cells(Rows.Count, "A").End(xlUp))
End With

For Each rCell In rng.Cells
With rCell
arr = Split(.Value, ":")
.Value = arr(0)
.Offset(0, 1) = arr(1)
End With
Next rCell
End Sub
'<<=============

 

[Tieu]

See if this can help.
Assume your A1 cell contains: SHEFFIELD UNITED:BLADES

use formula in B1
=LEFT(A1,FIND(":",A1)-1)

use this formula in C1
=RIGHT(A1,LEN(A1) - FIND(":",A1))