Split function invba

[dotchieJack]

Hello,
another question,
I have this in colum A
Name1 and Name2, Name3, Name4 (16)
I am looking for a piece of code that leaves
Name1 and Name2, Name3, Name4 in colom A
But puts ,split and puts (16) in colom B

 

[Chihiro]

Something like this. Change sheet and/or cells as needed.

Sub Test()
Dim x As Variant
With Sheet1
x = Split(.Cells(1, 1).Value, Chr(32), , vbTextCompare)
x = x(UBound(x))
.Cells(1, 1).Value = Trim(Replace(.Cells(1, 1).Value, x, ""))
.Cells(1, 2).NumberFormat = "@"
.Cells(1, 2).Value = Trim(x)
End With
End Sub

 

[dotchieJack]

Hoi Chichiro,
Nothing seems to work today
Example included

 

[Chihiro]

That's why it's good idea to post question with sample data.

The code won't work as you have blank for cells(1, 1) (i.e. A1).

Use this one.

Sub Test()
Dim x As Variant
Dim lRow As Long
Dim cel As Range

With Sheets("Blad1")
    lRow = .Cells(Rows.Count, 1).End(xlUp).Row
    For Each cel In .Range("A3:A" & lRow)
        x = Split(cel.Value, Chr(32), , vbTextCompare)
        x = x(UBound(x))
        cel.Value = Trim(Replace(cel.Value, x, ""))
        cel.Offset(0, 1).NumberFormat = "@"
        cel.Offset(0, 1).Value = Trim(x)
    Next cel
End With
End Sub

 

[Marc L]

Hi !​

dotchieJack, as you can start yourself just using Macro Recorder :

Sub Macro1()
'
' Macro1 Macro
' Macro enregistrée le 27/05/2016 par The Noob Simulator
'
     Range("A3:A4").Select
    Selection.Replace What:=")", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False
    Selection.TextToColumns Destination:=Range("A3"), DataType:=xlDelimited, _
        TextQualifier:=xlNone, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:="("
    Range("A1").Select
End Sub

​

 

[dotchieJack]

Hoi Chichiro and Marc,
Thank you for your help
(problem solved)

 

[jindon]

Name1 and Name2, Name3, Name4 (16)
I am looking for a piece of code that leaves
Name1 and Name2, Name3, Name4 in colom A
But puts ,split and puts (16) in colom B

Sub test()
    Dim r As Range
    For Each r In Range("a3", Range("a" & Rows.Count).End(xlUp))
        If r.Value Like "*(*" Then r.Resize(, 2) = Application.Trim(Split(Replace(r, "(", Chr(2) & "'("), Chr(2)))
    Next
End Sub

 

[dotchieJack]

Hello Jindon,
Thank you for your reply, looks like a very nice and short code, will test it soon
Have a nice weekend