Excel - Text to Rows & Columns?

Question

I have a ugly export string coming from another script and I'm wondering if this is possible: E.g. if you have string:

row1_c1,row2_c1,row3_c1|row1_c2,row_2_c2,row3_c3

whats the easiest way to convert to:

row1_c1    row1_c2 
row2_c1    row2_c2
row3_c1    row3_c2

If I copy-paste the string into Excel, the text-to-column conversion happens automatically. But how do I deal with the text-to-rows? Is this possible via UDF?
I tried:

Public Function SplitEnumbersDown(in_ As String)
  Dim Enumbers() As String
  Enumbers = Split(in_, ";")
  Selection(1).Resize(UBound(Enumbers) + 1) = Application.Transpose(Enumbers)
End Function

But if I Change the export string to
=SplitEnumbersDown("row1_c1,row2_c1,row3_c1")|=SplitEnumbersDown("row1_c2,row_2_c2,row3_c3") - this has a circular reference... Do I have a error somewhere?

I am aware of the text-to-column, and then paste Special with transpose, but I would like to have it done automatically.

So the end Goal is to copy paste the export string into Excel and automatically convert it.

You can use the Split Function or Regex or Text-to-column. And use the created Function on a Sub looping from the desired row to the lastRow. — danieltakeshi
– danieltakeshi, Commented Nov 17, 2017 at 11:17

CallumDA · Accepted Answer · 2017-11-17 14:12:20Z

1

This should work:

Option Explicit

Public Sub SplitString()
    Dim inputRange As Range, outputRange As Range
    Dim s() As String
    Dim al As Object
    Dim i As Long

    Set inputRange = ThisWorkbook.Worksheets("Sheet1").Range("A1")
    Set outputRange = inputRange.Offset(1, 0) 'output to cell(s) below
    Set al = CreateObject("System.Collections.ArrayList")

    s = Split(inputRange.Value, "|")
    For i = LBound(s) To UBound(s)
        al.Add Split(s(i), ",")
    Next i

    outputRange.Resize(UBound(v(0)), UBound(v)).Value = Application.Transpose(al.ToArray)  
End Sub

edited Nov 17, 2017 at 14:12

answered Nov 17, 2017 at 11:18

CallumDA

12.1k6 gold badges33 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

dv3 Over a year ago

Is this possible via UDF? with a sub a user would have to 1. copy-paste the export, 2. start the sub.... 1 step would be nicer

CallumDA Over a year ago

A UDF can't access the worksheet. It can only return a single value to the cell it is contained in. It's the same as a normal formula in that sense. You could have the function return an array and create an array formula on the worksheet. But if you have more rows than the array formula spans your user would have to extend the array formula -- meaning they have more than one step. Is there always going to be the same number of rows (3) ?

dv3 Over a year ago

Thanks - I just noticed that it won't work if I have an empty column... e.g. "row1_c1||row1_c3" .... it'll throw a type mismatch in the very last step.... ideas? or a new stack question?

dv3 Over a year ago

Hmmm so it'll work if the columns are all of the same size... I can adjust to that... But a more flexible solution would of course be sexy ;)

CallumDA Over a year ago

I’d ask a new question if you want some extra functionality (but also try to use this answer/other resources to build in the functionality yourself before you ask!)

Collectives™ on Stack Overflow

Excel - Text to Rows & Columns?

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related