{"id":1313,"date":"2018-04-03T00:54:46","date_gmt":"2018-04-02T18:54:46","guid":{"rendered":"http:\/\/xlfxs.com\/?p=1313"},"modified":"2018-04-03T00:54:46","modified_gmt":"2018-04-02T18:54:46","slug":"text-to-columns-dynamically-using-formulas","status":"publish","type":"post","link":"https:\/\/xlfxs.com\/?p=1313","title":{"rendered":"Text-to-Columns, dynamically using formulas"},"content":{"rendered":"

I often use the technique of concatenating columns into 1 cell with separators.<\/p>\n

Something like the CSV, 1 line that has all values for a single row (all columns for that row) into 1 text block.<\/p>\n

And then, because of that, I need to extract that back, into table<\/p>\n

<\/p>\n

I know there is “Text-to-Columns” in Excel, but I wanted that dynamically, so if source got changed, the columns output will be changed too.<\/p>\n

So, here is the example, having three formulas in two columns.<\/p>\n

Formula 1<\/strong> in column E starting 11 down (Cell E10 just has the value of 1)<\/p>\n

=IFERROR(SEARCH($D$8,$D$7,E10+1),\"---\")<\/code><\/p>\n

\"Screenshot_2\"<\/p>\n

 <\/p>\n

Formula 2<\/strong> in cell F11 only, to bring first item<\/p>\n

=IFERROR(MID($D$7,E10,E11-1),\"---\")<\/code><\/p>\n

\"Block2Rows6\"<\/p>\n

 <\/p>\n

Then, at last Formula 3<\/strong>, to get all additional columns from text block<\/p>\n

=IFERROR(MID($D$7,E11+LEN($D$8),E12-E11-LEN($D$8)),\"---\")<\/code><\/p>\n

\"Block2Rows5\"<\/p>\n

 <\/p>\n

I wanted to make it very clear and simple to show the case, data used in example is dummy data means nothing, just for sake of example.<\/p>\n