The sep_# LET variables help locate each ' ' seperator in the theme text. we use CHOOSE( mapped row 1, 2 or 3, first text, second text, third text). The formula you see on the left will be displayed for reference, while Excel will automatically convert the formula on the right into the appropriate result. Copy the cells in the table and paste into an Excel worksheet at cell A1. The second and third spaces separate each name component. The basic working of this formula is to use INT((ROW()-2)/3)+1 to map row numbers to indicate which theme in the list to extract, i.e. This example uses a two-part first name, Mary Kay. Excel 365), you might have to put each of the LET variables in its own helper column, then hide the helper column. In B2 and onwards: =LET(theme, INDEX(Sheet1!$B$2:$B$10000, INT((ROW()-2)/3)+1), sep_1, FIND(' ', theme), sep_2, FIND(' ', theme, sep_1+1), sep_3, LEN(theme), CHOOSE(MOD(ROW()-2,3)+1, LEFT(theme, sep_1-1), MID(theme, sep_1+1, sep_2-sep_1-1), RIGHT(theme, sep_3-sep_2))) Splitting a String into Multiple Columns using Convert Text to Columns Wizard Select the cell or column that contains the text you want to split. The header in Sheet2!A1 (and copy to B1): =Sheet1!A1 On a new sheet we will unwrap the source data: You can also do this with formulae, as long as there is always the same amount of themes to split.
