CODEWITHSUNDEEP
excelvba
Alexis Moreno
Alexis Moreno

Reputation: 415

Excel VBA- Copy and paste text on left after CHAR(10)

If the following conditions:

A1= fsfsdfsdfs

qewqeqewqe

B1= RIGHT(A1,FIND(CHAR(10),A1&" "))

Then B1 would display "qewqeqewqe".

This works only directly into an excel cell. How would I write this line of code given the following conditions above?

Something like the below?

Worksheets("sheet1").Cells(1,2).value= left(find(CHR(10), Worksheets("sheet1").Cells(1,1),&""))?????

I would not be asking if I had not searched for it. All I keep finding is how to do it in an excel file and not the VBA. Any help would be greatly appreciated. Thanks! 

Sub test()

Dim txt As String
Dim fullname As String

txt = Worksheets("Sheet1").Cells(1, 1).Value
fullname = Split(txt, Chr(10))

Worksheets("Sheet1").Cells(1, 2).Value = fullname

End Sub

Upvotes: 1

Views: 1851

Answers (1)

Davesexcel
Davesexcel

Reputation: 6984

This should do it

Sub Button1_Click()
    Dim rng As Range
    Dim i As Integer
    Dim Orig As Variant
    Dim txt As String

    Set rng = Range("A1")

    txt = rng.Value

    Orig = Split(txt, Chr(10))

    For i = 0 To UBound(Orig)
        Cells(1, i + 1) = Orig(i)
    Next i

End Sub

Check this out, http://www.xlorate.com/vba-examples.html#Split%20Cells

Upvotes: 1

Related Questions