split lines to columns

[franck.so]

Dear excel experts,

I would need to extract text lines from a single cell and distribute the result in separated columns.
Lines are separated by CHAR10. I would need a formula instead of using the "text to column" wizard.
All my cells are structured like this with number at the beginning:

1. GMI calibration using SAPHIR, should go to topic F on CAL/VAL
2. interesting inter-satellite calibration approach, could be moved as suggested above!
3.
4.
5.

if possible I would prefer to only extract the 2 first lines and not 3.,4.,5.
Could someone help me to find the best way to handle this?

thanks!
Franck

[hemesh]

Hello and Welcome to the forum Frank ! Can you please upload a sample book with atleast 10-15 rows of data and results expected to work with

to attach go to advanced --> select paper clip icon Or Manage attachment

[franck.so]

thanks for your swift answer!
Here attached a sample of the data (first sheet) and result expected (second sheet)
Hope this will help

[hemesh]

Assuming your data is in A1, try below in B1
=TRIM(MID(SUBSTITUTE($A1,CHAR(10),REPT(" ",500)),((COLUMN(A1)-COLUMN($A$1))*500)+1,500))
then drag to right till Column C

Hope this helps

[hemesh]

FInd attached !

[franck.so]

Thanks Hemesh and sorry for my late reply. This works fine and even if the formula is a bit complicated for me to understand, it helps and I can use it in my excel sheet.
Thanks again for you very efficient support!

Franck

[hemesh]

Hello Franck ! Thanks for the feedback ! IF your query is solved then mark thread as solved !