How do I split a cell into multiple rows

[barnard90]

I have excel sheet with Employee names data in it separated by semi colons ( ; ) . I need to split those Employee names into individual Employee names

The data in Worksheet1 in column1 is like this

Bob; Mattew;Roger -------> ( row 1 )

Terrie; Steve; Adams -------> ( row 2 )

Victor; Samuel -------> ( row 3 )


The data should be split and should be displayed in Worksheet2 in column1 ( Each employee in different row ) as

Bob
Mattew
Roger
Terrie
Steve
Adams


Could some one please suggest me an Excel Macro for this

Thanks

 

[Zathras]

You don't need a macro. (Although you could record the following steps if you want to.)

Use text-to-columns with the semi-colon as the delimiter.
Then cut and paste column B to the bottom of column A
Then cut and paste column C to the bottom of column A
etc. until all columns are pasted.
Sort the data to force the blank lines to the bottom.

Voila.

 

[barnard90]

There are atleast 960 rows inside the excel sheet
Cutting and pasting all these rows would be a real tough job

I hope a macro would be faster

 

[Zathras]

I didn't say anything about cutting and pasting rows.

What is the maximum number of names you have in any one cell? (Your post suggested that there were at most three.)

 

[barnard90]

Some of the cells contain as many as 10 -15 names

There are about 963 rows on the whole . It could increase
In all each name is separated from the other by " ; " in a cell.
So I need an easier way to separate the names and put them in different rows

Please suggest

Thanks

 

[Zathras]

So what's wrong with 10 or 15 cut and paste operations followed by a sort?

How many times a month will you need to do this?

 

[barnard90]

There are 15 elements and 960 rows

Are you suggesting I do 15 X 960 = 14400 operations ?

 

[Zathras]

I'm suggesting you do 1 + 14 + 14 = 29 operations.

1 Text to columns
14 sort each column to push the blanks to the bottom
14 cut and paste to the bottom of column A

If you want to semi-automate the process, you can manually do the text-to-columns, then use a macro to cut and paste one column to the bottom of column A.

If you look around here, there are several published methods for finding the last cell in a given column (which is the only tricky part of the macro.)

But for the 14 cut and paste to do manually would have
taken less time than all of this correspondence:
Select Column B
Sort (Do not expand)
Select B1
F8
Ctrl-Down
Ctrl-X
Select A1
Ctrl-Down
Down
Ctrl-V

Repeat, selecting C1, D1, etc.

 

[WinblowsME]

Sub split_names()
   Dim arr() As String, curr As Variant, i As Integer, j As Integer, last_row As Integer
   Dim data_sheet As String, target_sheet As String
   
   data_sheet = "Sheet1"
   target_sheet = "Sheet2"
   
   last_row = Sheets(data_sheet).Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
   
   j = 1
   
   For i = 1 To last_row
      If Trim(Sheets(data_sheet).Cells(i, 1)) = "" Then
         Exit For
      End If
         
      arr = Split(Sheets(data_sheet).Cells(i, 1), ";")
      
      For Each curr In arr
         Sheets(target_sheet).Cells(j, 1) = Trim(curr)
         j = j + 1
      Next
   Next i
End Sub