Formula(s) to Split Text String Across Columns Using Columns as Seperator

Hi All,

I have a query for you...

I have a spreadsheet, and in Column C I have text strings of addresses e.g:

Address Line 1, Address Line 1a, Town, City
Address Line 2, Town, City
Address Line 3, Address Line 3a, Town, City
Address Line 4, Address Line 4a, Town, City
Address Line 5, City
Address Line 6, Address Line 6a, Town, City

and so on...

What I have then done, is in Column D I have added the formula:

Formula:

Please Login or Register  to view this content.

This is intended to identify how many columns I need for each address line, in the above sample that would be 4, 2, 4, 4, 2, 4.

What I now want is a way of (using formulas) using the commas in the sentences to separate the text into the number of applicable columns.

I hope this makes sense...

I hope you can help!




Chris

Do you know that there is a built in feature to do this ? On the "Data" tab there is a "Text to Columns" feature that will parse string to separate columns based on the delimiter you provide it ... in your case the delimiter is ","

Hello Chris
Nimrod's answer is the simplest solution but if you have to use formulas to keep your original data the you could use a User Defined Function (UDF) for this, for example:

Please Login or Register  to view this content.

With your sample data in A1:A6 if would be:

Formula:

Please Login or Register  to view this content.

Copied across and down. The number of columns would have to be done manually. You could do a calculation of the maximum with the following Array formula (entered Ctrl+Shift+Enter):

Formula:

Please Login or Register  to view this content.

Hope this helps.
DBY