Text extraction from single cell to multiple cells

[Juniad]

Dear experts,
I am facing some challenging task and unable to find the solution. Attached the sample sheet where I want to extract the name, number etc. from single cell to multiple cells. Manual result added in sample sheet. Looking forward your response.

 

[GraH - Guido]

Maybe flash fill can be used.
Make sure you cover all scenarios in the examples you put in. Then highlight the full column and press CTRL+E. Repeat per extraction.
It's often a faster way for one shot analysis than writing formulae.
I'm without Excel and can't open your sample file, so appologies if the tip turns out to be very bad advice.

 

[shrivallabha]

Hi Juniad,
Please check if this works for the first part (English Name) with your data:
=LEFT(A3,MIN(IF(CODE(MID(A3&REPT(" ",200),ROW($A$1:$A$200),1))=63,ROW($A$1:$A$200),201))-1)

This is an array formula so needs to be committed by using CTRL+SHIFT+ENTER

 

[bosco_yip]

In B2, formula copied across and down :

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(CHAR(10)&$A2,":",CHAR(10)),CHAR(10),REPT(" ",199)),COLUMN(A1)*199,199))

 

[Juniad]

Impressive !