Forum Discussion

Deleted's avatar
Deleted
Jul 30, 2018

Excel - Extraction of content from a cell

Hello all,

 

I am new to this community.
I would love to have your help on the following question :

 

- I have an Excel sheet with a serie of numbers, always starting by the number "3".

- Each serie 13 characters long

- Sometimes I have 1 serie per cell, and sometimes I have up to 20 series into the same cell. The series are always clearly separated by a line break

 

This is my issue : I have to deliver the file with 1 serie maximum per cell! In each cell I should have "3333333333333".

 

Could you share a formula or any way I can solve this problem?

 

Thanks a lot for your input.

I attach a small extract of the file.

 

Best,

Iovan

excel
Formulas and Functions
  • Detlef_Lewin's avatar
    Detlef_Lewin
    Silver Contributor
    Jul 30, 2018

    Hello Iovan,

     

    unfortunately you did not show the desired result. So my guess would be this:

     

    Change the list into an Excel table (CTRL-T).

    Goto Data -> Get and transform -> From Table

    Now you are inside the query editor.

    Change the data type to Text.

    Split column by delimiter: #(lf)

    Split into Rows.

    Change data type to number.

    Close and load.

    Done.

     

     

Resources