Multiple Names in an Excel cell

Question

I have a situation where I have an excel file with several names in a cell for several rows down. I need to get those names out of the single cell, in a text to columns fashion, but also be able to sort them vertically rather than horizontally. For example, when using text to columns, the 4 names would be spread across 4 rows. I need the names to be listed vertically so they can be sorted alphabetically.

I've included an image of what I current have and I hope the description above makes sense to articulate what I'd like to see. I appreciate any help that can be provided for this. Thank you! 

Answer 1

Hi,

Using Excel's Data > Get & Transform tool, this should be easy to solve

1. Type a heading in cell A1
2. Select A1:A4 and press Ctrl+T
3. Click on cell A2 and go to Data > Get & Transform > From Table
4. In the Query Editor window, right click on the heading of the table and select Split column > By delimiter > Advanced Option > By rows > OK
5. Right click on the heading > Transform > Clean
6. Click on Close & Load

Hope this helps.

Answer 2

Sub Test()

  Dim Data, This

  Dim i As Long, j As Long

  Dim Where As Range, R As Range

  'Prepare

  Data = Array()

  'Get the used selected cells

  Set Where = Intersect(Selection, ActiveSheet.UsedRange)

  If Where Is Nothing Then Exit Sub

  'Visit each

  For Each R In Where.Cells

    'Skip empty cells

    If Not IsEmpty(R) Then

      'Split the contents by comma

      This = Split(R.Value, ",")

      'Resize our chache

      ReDim Preserve Data(0 To UBound(Data) + UBound(This) + 1)

      'Copy each item into

      For j = 0 To UBound(This)

        Data(i) = Trim$(This(j))

        i = i + 1

      Next

    End If

  Next

  'Transpose to vertical as 2D array

  Data = WorksheetFunction.Transpose(Data)

  'Create a new sheet

  Sheets.Add

  'Flush into

  Range("A1").Resize(UBound(Data), 1).Value = Data

End Sub

Answer 3

Sub Test()

  Dim Data, This

  Dim i As Long, j As Long

  Dim Where As Range, R As Range

 

  'Prepare

  Data = Array()

  'Get the used selected cells

  Set Where = Intersect(Selection, ActiveSheet.UsedRange)

  If Where Is Nothing Then Exit Sub

  'Visit each

  For Each R In Where.Cells

    'Skip empty cells

    If Not IsEmpty(R) Then

      'Split the contents by comma

      This = Split(R.Value, ",")

      'Resize our chache

      ReDim Preserve Data(0 To UBound(Data) + UBound(This) + 1)

      'Copy each item into

      For j = 0 To UBound(This)

        Data(i) = Trim$(This(j))

        i = i + 1

      Next

    End If

  Next

  'Transpose to vertical as 2D array

  Data = WorksheetFunction.Transpose(Data)

  'Create a new sheet

  Sheets.Add

  'Flush into

  Range("A1").Resize(UBound(Data), 1).Value = Data

End Sub

Hello Andreas,

I fear that my skillset in excel is not sufficient to accurately decipher what I should do with what you shared. I honestly have no clue where to begin with your response. Can you please clarify?

Answer 4

Hi,

Using Excel's Data > Get & Transform tool, this should be easy to solve

1. Type a heading in cell A1
2. Select A1:A4 and press Ctrl+T
3. Click on cell A2 and go to Data > Get & Transform > From Table
4. In the Query Editor window, right click on the heading of the table and select Split column > By delimiter > Advanced Option > By rows > OK
5. Right click on the heading > Transform > Clean
6. Click on Close & Load

Hope this helps.

Thanks for your reply. I believe what you shared is only accessible in Excel 2016. I am working with 2013. Is there a way to do this in the 2013 version? Thanks again!

Answer 5

I honestly have no clue where to begin with your response. Can you please clarify?

Have a look here to insert the macro:

https://www.ablebits.com/office-addins-blog/2013/12/06/add-run-vba-macro-excel/

Save the file as .XLSM

Select all cells that contains the names

Press ALT-F8 and run the macro

Andreas.