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

 

[Sektor]

Are carriage returns located in the end of line? What I mean is:

Muhammad Shukri bin Hussain [CR]
841230-01-5547 [CR]
Timbalan Pendakwa Raya [CR]
Unit Pendakwaan Negeri Sembilan [CR]
(Mahkamah Sesyen Kuala Pilah)

Can you post workbook with some data? Actually, there must be Line Feed if you pressed Alt+Enter in a cell, so try this (extracts only name):

[COLOR="Blue"]Function[/COLOR] ParseData(Str [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]String[/COLOR]) [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]String[/COLOR]

    [COLOR="Blue"]Dim[/COLOR] arr [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Variant[/COLOR]
    
    Application.Volatile
    
    arr = Split(Str, vbLf)
    
    ParseData = arr(0)

[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Function[/COLOR]

 

[KoE]

Are carriage returns located in the end of line? What I mean is:

Muhammad Shukri bin Hussain [CR]
841230-01-5547 [CR]
Timbalan Pendakwa Raya [CR]
Unit Pendakwaan Negeri Sembilan [CR]
(Mahkamah Sesyen Kuala Pilah)

Can you post workbook with some data? Actually, there must be Line Feed if you pressed Alt+Enter in a cell, ...

my bad, they are separated by line feed (just didn't know the exact terminology)

 

[Sektor]

[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], iUpper [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Integer[/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 = 1 [COLOR="Blue"]To[/COLOR] Cells(Rows.Count, "I").End(xlUp).Row
        arr = Split(Cells(i, "I"), vbLf)
        iUpper = [COLOR="Blue"]UBound[/COLOR](arr)
        [COLOR="Blue"]With[/COLOR] sh
            j = j + 1
            .Cells(j, "A") = arr(0)
            .Cells(j, "B") = arr(1)
            .Cells(j, "L") = arr(2)
            [COLOR="Blue"]For[/COLOR] j = 3 [COLOR="Blue"]To[/COLOR] iUpper
                s = s & arr(j) & " "
            [COLOR="Blue"]Next[/COLOR]
            .Cells(j, "M") = Left$(s, Len(s) - 1)
        [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]With[/COLOR]
    [COLOR="Blue"]Next[/COLOR]

[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]

 

[KoE]

Sektor,

Thanks for your fast response but I'm getting run-time error '9': subscript out of range

 

[anandvarma]

try this

Excel Workbook
	A	B	C	D
1	Muhammad Shukri bin Hussain 841230-01-5547 Timbalan Pendakwa Raya Unit Pendakwaan Negeri Sembilan (Mahkamah Sesyen Kuala Pilah)	Muhammad Shukri Bin Hussain	*	06/10/2008 05/10/2010 05/10/2011
2	Cindy Jasmine Enduyan Anak Julin 850319-13-5670 Peguam Persekutuan Unit Guaman Jabatan Peguam Negara Cawangan Wilayah Persekutuan Kuala Lumpur	Cindy Jasmine Enduyan Anak Julin	*	03/02/200902/02/2011 02/02/2012
3
4	Muhammad Shukri bin Hussain	841230-01-5547	Timbalan Pendakwa Raya Unit	Pendakwaan Negeri Sembilan (Mahkamah Sesyen Kuala Pilah)
5	Cindy Jasmine Enduyan Anak Julin	850319-13-5670	Peguam Persekutuan Unit	Guaman Jabatan Peguam Negara Cawangan Wilayah Persekutuan Kuala Lumpur
Sheet1

 

[KoE]

my apology for asking too many things all at once and got helpful people here confused.

lets me re-word my wishes in a more coherent manner.

<bold>scenario</bold>: i have data in one excel file that i need to copy to another file.

<bold>problem</bold>: the data in col I as shown below although looks nice but cannot be processed since it contains too many information within the cell. The data contains name followed by ID (xxxxxx-xx-xxxx) followed by post and lastly location. They are separated by line feed. I cannot mess the original file since it belongs to another person.

Excel Workbook
	I
6	Muhammad Shukri bin Hussain841230-01-5547Timbalan Pendakwa RayaUnit Pendakwaan Negeri Sembilan(Mahkamah Sesyen Kuala Pilah)
7	Cindy Jasmine Enduyan Anak Julin850319-13-5670Peguam PersekutuanUnit GuamanJabatan Peguam NegaraCawangan Wilayah PersekutuanKuala Lumpur
8	Nur Azzuin binti Abdul Moati831111-01-6142Peguam PersekutuanUnit GuamanJabatan Peguam NegaraCawangan Wilayah PersekutuanKuala Lumpur
Sheet

<bold>my intention</bold>: to copy/import these info to another excel file; name to col A, ID to col B, post to col L and location to M in the new file as shown below.

Excel Workbook
	A	B	L	M
1	Nama	No Kad Pengenalan	Gelaran Jawatan	Bahagian/Tempat Bertugas
2	*	*	*	*
3	*	*	*	*
Sheet

any taker? thanks!

 

[Sektor]

Actually, my code works. However, I don't know what row your range begins with.
Try to change i = 1 to i = 6 in my code.

 

[KoE]

Actually, my code works. However, I don't know what row your range begins with.
Try to change i = 1 to i = 6 in my code.

i'm stuck. keep getting subscript error should i send you the original file and the target new file?

 

[kpark91]

Slight variation of Sektor's code

Try

Sub ExtractData()

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