I am uploading a CSV file to a website. I need to convert data in one of the fields (i.e. column of cells) to serialised data for the website plugin to accept it and populate correctly.
I have it in this form (this is an example)
Monday 11 May, Afternoon, Monday 11 May, Evening, Friday 15 May, Morning, Friday 15 May, Afternoon, Friday 15 May, Evening, Saturday 16 May, Morning, Saturday 16 May, Afternoon, Saturday 16 May, Evening, Sunday 17 May, Morning, Sunday 17 May, Afternoon, Sunday 17 May, Evening
I need to convert it to
a:11:{i:0;s:24:”Monday 11 May, Afternoon”;i:1;s:22:”Monday 11 May, Evening”;i:2;s:22:”Friday 15 May, Morning”;i:3;s:24:”Friday 15 May, Afternoon”;i:4;s:22:”Friday 15 May, Evening”;i:5;s:24:”Saturday 16 May, Morning”;i:6;s:26:”Saturday 16 May, Afternoon”;i:7;s:24:”Saturday 16 May, Evening”;i:8;s:22:”Sunday 17 May, Morning”;i:9;s:24:”Sunday 17 May, Afternoon”;i:10;s:22:”Sunday 17 May, Evening”;}
Where a is the number of terms (‘Sunday 17 May, Afternoon’ counts as 1 term)
i is the a-1 ordinal number of the following term
s is the character count, including commas and spaces in the following term.
I need a formula or macro to convert each cell to that format - the ‘a’ count of terms varies from entry to entry.
please attach an example.ods which shows unambiguous how your data is organized in the spreadsheet,
use the ⇑ -upload button in the middle of the toolbar above the edit-frame.
Here is an example of the data. 2 records with an id number (user) for each. The second column has 2 examples of the data that needs transrorming.
example of data.ods (13.4 KB)