Easy, treat each column separately copy third column to an editor e.g. crimson editor then replace each comma with a tab and paste back to excel.

./TheMburu

On Wed, Jul 10, 2013 at 8:55 PM, Anthony Tai <jicholatai@gmail.com> wrote:

Create a macro in Excel. Add this code to your macro, which will turn your comma-delimited text to rows in Excel.

Sub SplitAndTranspose()

Dim N() As String

N = Split(ActiveCell, ", ") // this comma has a space after it, so put yours as row appears

ActiveCell.Resize(UBound(N) + 1) = WorksheetFunction.Transpose(N)

End Sub




Once you've created the macro highlight your cell and run the macro. That's it!




Regards

On Wed, Jul 10, 2013 at 8:44 PM, Thuo Wilson <lixton@gmail.com> wrote:

On 10 July 2013 17:23, wa <wanangu@gmail.com> wrote:

@thuo see link

http://office.microsoft.com/en-001/excel-help/split-text-into-different-cells-HA102809804.aspx

Thanks, but this is text to column. I need text to rows.

WIlson 

On Wed, Jul 10, 2013 at 5:12 PM, Thuo Wilson <lixton@gmail.com> wrote:

Hey,

Supposing i had an excel document like the attached with a thousands rows and within a cell are multiple values separated by commas i want to spread to other rows. How do i do it?

See example; How do i spread column 3 so that each item/valu appear on its own row. Mind you i have a thousands row with 3rd column with values in excess of 100, so manual is attainable but tiresome.

Angola	Mobile	2449
Anguilla	Fixed	1264
Anguilla	Mobile	126453, 126458, 126472, 126477, 1264235, 1264469, 1264476
Antigua & Barbuda	Fixed	1268
Antigua & Barbuda	Mobile	126872, 126873, 126876, 126877, 126878, 1268464, 1268764
Argentina	Fixed	54
Argentina	Mobile	549
Armenia	Fixed	374
Armenia	Mobile	3744, 3745, 3747, 3749, 37460
Aruba	Fixed	297
Aruba	Mobile	2975, 2976, 2979, 29756, 29758, 29759, 29773, 29774, 29796, 29799...
Ascension Island	Fixed	247
Australia	Fixed	61, 6128, 6129, 6138, 6139, 6173, 6186, 6187, 61261, 61262
Australia	Mobile	614, 6115, 6116, 6117, 6118, 6119
Australia	Tollfree	611800
Australia	Tollfree (Series 611300)	611300

_______________________________________________
skunkworks mailing list
skunkworks@lists.my.co.ke
------------
List info, subscribe/unsubscribe
http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks
------------

Skunkworks Rules
http://my.co.ke/phpbb/viewtopic.php?f=24&t=94
------------
Other services @ http://my.co.ke

--
Walter Nyamweya

_______________________________________________
skunkworks mailing list
skunkworks@lists.my.co.ke
------------
List info, subscribe/unsubscribe
http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks
------------

Skunkworks Rules
http://my.co.ke/phpbb/viewtopic.php?f=24&t=94
------------
Other services @ http://my.co.ke

_______________________________________________
skunkworks mailing list
skunkworks@lists.my.co.ke
------------
List info, subscribe/unsubscribe
http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks
------------

Skunkworks Rules
http://my.co.ke/phpbb/viewtopic.php?f=24&t=94
------------
Other services @ http://my.co.ke

--

Anthony Tai

"A good head and a good heart are always a formidable combination."

_______________________________________________
skunkworks mailing list
skunkworks@lists.my.co.ke
------------
List info, subscribe/unsubscribe
http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks
------------

Skunkworks Rules
http://my.co.ke/phpbb/viewtopic.php?f=24&t=94
------------
Other services @ http://my.co.ke

--
Conservatism is the adherence to the old tried against the new untried.

_______________________________________________
skunkworks mailing list
skunkworks@lists.my.co.ke
------------
List info, subscribe/unsubscribe
http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks
------------

Skunkworks Rules
http://my.co.ke/phpbb/viewtopic.php?f=24&t=94
------------
Other services @ http://my.co.ke