how to convert a row to columns?

[mohsin4rasheed]

Hi
i am beginner plz help me
i have row like this
+359 89 225 0572, +66 88 395 9003, +966 50 028 4406, +966 50 079 9716,
want to convert in column like
+359 89 225 0572,
+66 88 395 9003,
+966 50 028 4406,
+966 50 079 9716,
how can i do in excel?

 

[BenMcBen]

copy, paste special and check the "transpose" check box, or use the transpose function in an array formula

 

[alansidman]

In power query, split the column using the delimiter ",". Then unpivot the rows.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}, {"Column1.3", type text}, {"Column1.4", type text}, {"Column1.5", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"})
in
    #"Removed Columns"

Book1
	A
1	Value
2	+359 89 225 0572
3	+66 88 395 9003
4	+966 50 028 4406
5	+966 50 079 9716
6
Sheet2

MCode
select your range/table (blue)
Data - From Table
it will open Power Query Editor
find and open Advanced Editor
replace whole code there with code from the post
be sure the name of the source table is the same as in the code (here: Table1)
Done
Close&Load

 

[kweaver]

Welcome to MrExcel.
If your data is in, say, A1, this will split the data into separate cells if you put the formula in, say, A3, and fill across to the right.

=TRIM(MID(SUBSTITUTE($A1,",",REPT(" ",999)),COLUMNS($A:A)*999-998,999))

Then, you can select that row, do a COPY, and then Paste Special Transpose with the active cell being, for example, in A4.

(PS: do you really want the commas after each number?)

 

[kweaver]

Here's a possible VBA routine:

Sub TransposeRange()
Dim rng As Range
Dim InputRng As Range, OutRng As Range
Set InputRng = Application.Selection.Range("A1")  ' data in A1
Set OutRng = Application.InputBox("Output to (single cell):", xTitleId, Type:=8) 'prompt for starting cell for output
Arr = VBA.Split(InputRng.Range("A1").Value, ",")
OutRng.Resize(UBound(Arr) - LBound(Arr) + 1).Value = Application.Transpose(Arr)
End Sub