How do I split one string into two?

Hello,

Please see the attached Excel file.  Using VB code, I want to be split each string in column 'A' into 2 separate strings.  The seperator for the two strings could be the '>' symbol.  Any idea how to impliment this?

Thanks,

mvem
Example.xlsx
mvemAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DaveCommented:
A simple VBA text to columns

hth

Dave

Sub Macro1()
Columns("A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, OtherChar _
        :=">"
End Sub

Open in new window

0
Mark WillsTopic AdvisorCommented:
Think dave has given you the answer, but I was wondering, why does it have to be VB code ?
0
mvemAuthor Commented:
Hmm, just tried this and it didn't split anything up.  I was expecting for there two be three columns (A,B and C).  'A' would contain the original string, and 'B' and 'C' would contain the splits.
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

DaveCommented:
If you do have > in the string then the split will occur in A&B

This version, which includes a working file version puts the split in B and C. Click the button to run

Dave

Sub Macro1()
Columns("B").Insert
Columns("A").TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, OtherChar _
        :=">"
End Sub

Open in new window

FIle.xls
0
mvemAuthor Commented:
Hey Dave,

Please see attached file for results.  An exact copy of column 'A' is placed into column 'B'.

mvem
Result.jpg
0
HyperBPPCommented:

Sub splitA()
    On Error Resume Next
    Dim index As Long
    Dim tempArray() As String
    
    With ActiveSheet.UsedRange.Columns(1)
        For index = 1 To .Cells.Count
            tempArray = Split(.Cells(index), " ")
            ActiveSheet.Range("B" & index).Value = tempArray(0)
            ActiveSheet.Range("C" & index).Value = tempArray(1)
        Next
    End With
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mvemAuthor Commented:
Works like a charm...thank you.
0
DaveCommented:
Pls substitute this
  Columns("A").TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, Other:=True, OtherChar _
        :=">"
in place of
Columns("A").TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, OtherChar _
        :=">"

Dave

0
DaveCommented:
While this is closed, this is not a job for a Loop. It is a inefficient method given there are superior alternatives

Regards

Dave
0
Mark WillsTopic AdvisorCommented:
And interesting to see no response to "why VB"
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.