Forum Discussion

Barbara Floyd's avatar
Barbara Floyd
Copper Contributor
Jul 31, 2017

Splitting 1 row into multiple tows

Hi
I have a report extracted into Excel. One of the columns is called 'employees'. a single row has multiple employees in the employees column. Is there a way to split this out to contain a row per employee? If I extract the report in .csv format, there is <br/> between each employee. I'm a novice so thanks in advance for any ideas, really appreciated!
Formulas & Functions
Need Help

1 Reply

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jul 31, 2017

    Hi Barbara,

     

    How do you import your HTML into the Excel? It shall correctly recognize <br/> tag. Small test:

    HTML code

    <tr><td>
Name 1<br/>Name 2<br/>
</td><tr>
<tr><td>
Name 3
</td></tr>

    in browser looks like

    which is copy/pasted to Excel as

     

    Anyway, one of the way to correct result is in your csv file replace <br/> on any symbol which definitely not in your names, let say "|".  After that Data->Text to columns using above symbol as delimiter. After that select cells wilt names, Paste Special->Transpose.

     

    Above is not a good way - manual and if list of names is not quite big. Better to sort out the transformation from HTML-like to Excel. But without the sample can't be more concrete.

     

Resources