Convert Rows into three columns from a cell

[azeem8202]

Hi everyone,

Needs your support for given situation.

I have one cell (sheet - 1) in which I enter the name , employee number and functional title of many employees. i want to extract name, employee code and functional title in three different columns another sheet (sheet - 2 ) first line of cell (Following staff has not yet attended the IB training remains constant.

content of the cell are as follows:

Following staff has not yet attended the training:
Mr. A B C (111 111) - Operational Officer
Mr. P D S (222 222) - Relationship Manager

Note: 1. Number of employees may be 1 or more than 1.
2. For next line I enter Alt+enter.

I want to extract columns like this:
Column A Column B Column C
A B C 111 111 Operational Officer

 

[mumps]

Assuming your data is in column A starting at row 2, place the formulas in B2, C2 and D2 and then copy them down as far as your data goes:
=LEFT(A2,FIND("(",A2,1)-2)
=MID(A2,SEARCH("(",A2)+1,SEARCH(")",A2)-SEARCH("(",A2)-1)
=MID(A2, FIND("- ", A2) + 2, LEN(A2))

 

[azeem8202]

Thanks for reply.

Following content are placed in one row.
"Following staff has not yet attended the training:
Mr. A B C (111 111) - Operational Officer
Mr. P D S (222 222) - Relationship Manager"

 

[mumps]

Are you saying that

"Following staff has not yet attended the training:
Mr. A B C (111 111) - Operational Officer
Mr. P D S (222 222) - Relationship Manager"

is all in one cell? Are the quotation marks included in the cell?

 

[azeem8202]

Yes, all content are in one cell without quotation marks.

 

[mumps]

Start with a blank Sheet2. The macro assumes your data starts in row 2 of Sheet1.

Sub SplitInfo()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Sheets("Sheet1").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim rng As Range
    Dim vRng As Variant
    Dim i As Long
    With Sheets("Sheet2")
        .Cells(1, 1) = "Following staff has not yet attended the IB training."
        .Range("A2:C2") = Array("Name", "Employee Number", "Functional Title")
    End With
    For Each rng In Sheets("Sheet1").Range("A2:A" & LastRow)
        vRng = Split(rng, Chr(10))
        For i = 1 To UBound(vRng)
            Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) = Trim(Mid(vRng(i), 4, WorksheetFunction.Find("(", vRng(i), 1) - 4))
            Sheets("Sheet2").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0) = Mid(vRng(i), WorksheetFunction.Search("(", vRng(i)) + 1, WorksheetFunction.Search(")", vRng(i)) - WorksheetFunction.Search("(", vRng(i)) - 1)
            Sheets("Sheet2").Cells(Rows.Count, "C").End(xlUp).Offset(1, 0) = Split(vRng(i), " - ")(1)
        Next i
    Next rng
    Application.ScreenUpdating = True
End Sub

 

[azeem8202]

Can this be done without macro??? if i dont want to use macro.

 

[FormR]

Can this be done without macro???

Hi, here is one option you can try, note the formula in column B relies on the result from the formula in column A.

Excel 2013/2016
	A	B	C
1	Col A	Col B	Col C
2	Mr. A B C	111 111	Operational Officer
3	Mr. P D S	222 222	Relationship Manager
4	Mr Z X Spectrum	81	IT Manager
5
Sheet2

Cell Formulas
Range	Formula
A2	=TRIM(LEFT(SUBSTITUTE(TRIM(MID(SUBSTITUTE(Sheet1!$A$1,CHAR(10),REPT(" ",LEN(Sheet1!$A$1))),(ROWS(A$2:A2)+1)*LEN(Sheet1!$A$1)-(LEN(Sheet1!$A$1)-1),LEN(Sheet1!$A$1))),"(",REPT(" ",LEN(Sheet1!$A$1))),LEN(Sheet1!$A$1)))
B2	=MID(TRIM(LEFT(SUBSTITUTE(TRIM(MID(SUBSTITUTE(Sheet1!$A$1,CHAR(10),REPT(" ",LEN(Sheet1!$A$1))),(ROWS(B$2:B2)+1)*LEN(Sheet1!$A$1)-(LEN(Sheet1!$A$1)-1),LEN(Sheet1!$A$1))),")",REPT(" ",LEN(Sheet1!$A$1))),LEN(Sheet1!$A$1))),LEN($A2)+3,LEN(Sheet1!$A$1))
C2	=TRIM(RIGHT(SUBSTITUTE(TRIM(MID(SUBSTITUTE(Sheet1!$A$1,CHAR(10),REPT(" ",LEN(Sheet1!$A$1))),(ROWS(C$2:C2)+1)*LEN(Sheet1!$A$1)-(LEN(Sheet1!$A$1)-1),LEN(Sheet1!$A$1))),"-",REPT(" ",LEN(Sheet1!$A$1))),LEN(Sheet1!$A$1)))

Excel 2013/2016
	A
1	Following staff has not yet attended the training: Mr. A B C (111 111) - Operational Officer Mr. P D S (222 222) - Relationship Manager Mr Z X Spectrum (81) - IT Manager
Sheet1

 

[azeem8202]

thank FormR for you reply. while applying this formula to my query , details of first employee (Mr. A B C ) does not separated in to three columns.

 

[azeem8202]

FormR kindly ignore my last msg.

and a lot thanks for your prompt response.