Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Splitting text in a cell
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Splitting text in a cell
Use Text to Columns:
1. select the column 2. pull-down Data Text to Columns then select delimited then select the colon -- Gary's Student "TheRook" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Splitting text in a cell
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 '<<============= --- Regards, Norman "TheRook" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Splitting text in a cell
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)) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Splitting text in a cell | Excel Worksheet Functions | |||
Splitting text in a cell | Excel Discussion (Misc queries) | |||
splitting text from one cell | Excel Discussion (Misc queries) | |||
splitting text in a cell.. | Excel Discussion (Misc queries) | |||
Splitting Up Text in One Cell To Many | Excel Worksheet Functions |