Manipulating Text, Numbers and Dates

[KoE]

Greetings Excel experts!,

Been a while since my last posting and i'm still very much a novice, so your help is much sought after and appreciated. Below is the scenario:

I've data in one messy worksheet labelled 41 that looks like below which I plan to do these few processes:

1. import the data in col I to another worksheet labelled Proposed (in a new workbook) and separate the data into 4 columns; A, B, L & M. Looking at sample data below, each cell in col I actually contains 4 distinct info; name followed by ID xxxxxx-xx-xxxx followed by the post (separated from ID by carriage return) and lastly the location (also separated from post by carriage return). Name should go to col A, ID to col B, post to col L and location to M;

2. import the data in col M in that similar messy worksheet into the same worksheet as in 1 but into col N, O and P. The data in original col M are actually dates dd/mm/yyyy. Some dates are punctuated not only by carriage return but also with a dash "-".

I could "extract" out name as shown in col K via proper/trim/left but that's the maximum my knowledge in excel

as stated earlier, any help towards the targets in 1 & 2 is much sought after and appreciated. Thanks!

Excel Workbook
	I	K	L	M
6	Muhammad Shukri bin Hussain841230-01-5547Timbalan Pendakwa RayaUnit Pendakwaan Negeri Sembilan(Mahkamah Sesyen Kuala Pilah)	Muhammad Shukri Bin Hussain	*	06/10/200805/10/201005/10/2011
7	Cindy Jasmine Enduyan Anak Julin850319-13-5670Peguam PersekutuanUnit GuamanJabatan Peguam NegaraCawangan Wilayah PersekutuanKuala Lumpur	Cindy Jasmine Enduyan Anak Julin	*	03/02/2009-02/02/201102/02/2012
Sheet

 

[KoE]

Many thanks to Sektor & kpark91

I've ran both codes. Slight adjustment to Sektor's i =6 gave me as below. As you could see, it misses good number of data and it appends all locations in col M.

Excel Workbook
	A	B	L	M
1	Muhammad Shukri bin Hussain	841230-01-5547	Timbalan Pendakwa Raya	*
2	*	*	*	*
3	*	*	*	Unit Pendakwaan Negeri Sembilan (Mahkamah Sesyen Kuala Pilah) Unit Guaman Jabatan Peguam Negara Cawangan Wilayah Persekutuan Kuala Lumpur Unit Guaman Jabatan Peguam Negara Cawangan Wilayah Persekutuan Kuala Lumpur *Unit Guaman Jabatan Peguam Negara Cawangan Wilayah Persekutuan Kuala Lumpur Unit Guaman Pejabat Penasihat Undang-Undang Negeri Sembilan Unit Guaman Pejabat Penasihat Undang-Undang Negeri Pahang
4	Fatin Azimah binti Abd Aziz	851224-14-5168	Peguam Persekutuan	Unit Pendakwaan Negeri Sembilan (Mahkamah Sesyen Kuala Pilah) Unit Guaman Jabatan Peguam Negara Cawangan Wilayah Persekutuan Kuala Lumpur Unit Guaman Jabatan Peguam Negara Cawangan Wilayah Persekutuan Kuala Lumpur *Unit Guaman Jabatan Peguam Negara Cawangan Wilayah Persekutuan Kuala Lumpur Unit Guaman Pejabat Penasihat Undang-Undang Negeri Sembilan Unit Guaman Pejabat Penasihat Undang-Undang Negeri Pahang Bahagian Guaman Jabatan Peguam Negara Unit Pendakwaan Negeri Pulau Pinang (Mahkamah Majistret Jawi) Jabatan Kastam Diraja Malaysia Kementerian Perdagangan Dalam Negeri, Koperasi dan Kepenggunaan Unit Pendakwaan Negeri Pahang (Mahkamah Majistret Kuantan) Kementerian Dalam Negeri Unit Pendakwaan Negeri Kelantan (Mahkamah Majistret Tanah Merah) Jabatan Kastam Diraja Malaysia Unit Pendakwaan Negeri Sabah (Mahkamah Majistret Kota Kinabalu) Unit Pendakwaan Negeri Perak (Mahkamah Majistret Grik) Unit Pendakwaan Negeri Pahang (Mahkamah Majistret Kuantan) Unit Pendakwaan Negeri Perak (Mahkamah Majistret Manjung) Kementerian Perdagangan Dalam Negeri, Koperasi dan Kepenggunaan Bahagian (i deleted good number of lines to save space)
Sheet

kpark91's codes manage to break down all the data, list them nicely in cols A, B & L but for col M, it "adds in" the data on location from row 1 to row 2 and so on similar to Sektor's code.

Excel Workbook
	A	B	L	M
1	Muhammad Shukri bin Hussain	841230-01-5547	Timbalan Pendakwa Raya	Unit Pendakwaan Negeri Sembilan (Mahkamah Sesyen Kuala Pilah)
2	Cindy Jasmine Enduyan Anak Julin	850319-13-5670	Peguam Persekutuan	Unit Pendakwaan Negeri Sembilan (Mahkamah Sesyen Kuala Pilah) Unit Guaman Jabatan Peguam Negara Cawangan Wilayah Persekutuan Kuala Lumpur
3	Nur Azzuin binti Abdul Moati	831111-01-6142	Peguam Persekutuan	Unit Pendakwaan Negeri Sembilan (Mahkamah Sesyen Kuala Pilah) Unit Guaman Jabatan Peguam Negara Cawangan Wilayah Persekutuan Kuala Lumpur Unit Guaman Jabatan Peguam Negara Cawangan Wilayah Persekutuan Kuala Lumpur
Sheet

further comments/improvements are highly appreciated ! thanks!

 

[Sektor]

What must be in column M? The thing is that code "grabs" all remained rows in cell!

 

[KoE]

What must be in column M? The thing is that code "grabs" all remained rows in cell!

kpark91's codes are okay except that the codes grabs the post from other rows and append them incrementally

Excel Workbook
	A	B	L	M
1	Muhammad Shukri bin Hussain	841230-01-5547	Timbalan Pendakwa Raya	Unit Pendakwaan Negeri Sembilan (Mahkamah Sesyen Kuala Pilah)
2	Cindy Jasmine Enduyan Anak Julin	850319-13-5670	Peguam Persekutuan	Unit Pendakwaan Negeri Sembilan (Mahkamah Sesyen Kuala Pilah) Unit Guaman Jabatan Peguam Negara Cawangan Wilayah Persekutuan Kuala Lumpur
3	Nur Azzuin binti Abdul Moati	831111-01-6142	Peguam Persekutuan	Unit Pendakwaan Negeri Sembilan (Mahkamah Sesyen Kuala Pilah) Unit Guaman Jabatan Peguam Negara Cawangan Wilayah Persekutuan Kuala Lumpur Unit Guaman Jabatan Peguam Negara Cawangan Wilayah Persekutuan Kuala Lumpur
Sheet

the output should look like below

<table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:291px;" /><col style="width:140px;" /><col style="width:235px;" /><col style="width:1790px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td >*</td><td >A</td><td >B</td><td >L</td><td >M</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Muhammad Shukri bin Hussain</td><td >841230-01-5547</td><td >Timbalan Pendakwa Raya</td><td >Unit Pendakwaan Negeri Sembilan (Mahkamah Sesyen Kuala Pilah)</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >Cindy Jasmine Enduyan Anak Julin</td><td >850319-13-5670</td><td >Peguam Persekutuan</td><td >Unit Guaman Jabatan Peguam Negara Cawangan Wilayah Persekutuan Kuala Lumpur</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >Nur Azzuin binti Abdul Moati</td><td >831111-01-6142</td><td >Peguam Persekutuan</td><td >Unit Guaman Jabatan Peguam Negara Cawangan Wilayah Persekutuan Kuala Lumpur </td></tr></table>

 

[Sektor]

I missed important thing (highlighted with red)!

[COLOR="Blue"]Sub[/COLOR] ExtractData()

    [COLOR="Blue"]Dim[/COLOR] s [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]String[/COLOR]
    [COLOR="Blue"]Dim[/COLOR] i [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Long[/COLOR], j [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Long[/COLOR], k [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Long[/COLOR]
    [COLOR="Blue"]Dim[/COLOR] arr [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Variant[/COLOR], sh [COLOR="Blue"]As[/COLOR] Worksheet
    
    [COLOR="Blue"]Set[/COLOR] sh = Worksheets("Proposed")
    
    [COLOR="Blue"]For[/COLOR] i = 6 [COLOR="Blue"]To[/COLOR] Cells(Rows.Count, "I").End(xlUp).Row
        arr = Split(Cells(i, "I"), vbLf)
        [COLOR="Blue"]With[/COLOR] sh
            j = j + 1
            .Cells(j, "A") = arr(0)
            .Cells(j, "B") = arr(1)
            .Cells(j, "L") = arr(2)
            [COLOR="Red"][B]s = vbNullString[/B][/COLOR]
            [COLOR="Blue"]For[/COLOR] k = 3 [COLOR="Blue"]To[/COLOR] [COLOR="Blue"]UBound[/COLOR](arr)
                s = s & arr(k) & " "
            [COLOR="Blue"]Next[/COLOR] k
            .Cells(j, "M") = Left$(s, Len(s) - 1)
        [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]With[/COLOR]
    [COLOR="Blue"]Next[/COLOR] i
    
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]

 

[KoE]

Sektor,

ran the code and got run-time error '5' : invalid procedure call or argument

 

[Sektor]

All works fine for me.
How did you call it? The sheet with those cells must be active.

 

[KoE]

Sektor,

i've modified your code into this

Sub ExtractData()

    Dim s As String
    Dim i As Long, j As Long, k As Long
    Dim arr As Variant, sh As Worksheet
    Dim LastRow As Long
    
    Set sh = Worksheets("Proposed")
    
    For i = 6 To Cells(Rows.Count, "I").End(xlUp).Row
        arr = Split(Cells(i, "I"), vbLf)
        With sh
            j = j + 1
            .Cells(j, "A") = arr(0)
            .Cells(j, "B") = arr(1)
            .Cells(j, "L") = arr(2)
            For k = 3 To UBound(arr)
                s = s & arr(k) & " "
                .Cells(j, "M") = Left$(s, Len(s) - 1)
            Next k
            s = vbNullString
        End With
    Next i
    
End Sub

and it ran sweetly! thanks for the head up

 

[Sektor]

Finally! Although don't see any difference in reposition s variable.

 

[KoE]

next:

how do I modify that code so that the data will only be inserted into row 2 onwards?

 

[Sektor]

Add "j = 1" before For loop.