Want to seperate name, age and designation

[exceldoubts]

Hi,

I want the name, age and position get separated in different columns. I have attached sample file. Need a quick solution.

Thank you

[Phuocam]

Try this in B2:

=LEFT($A2,MATCH(TRUE,INDEX(ISNUMBER(--MID($A2,ROW(INDIRECT("1:"&LEN($A2))),1)),0),0)-1)

in C2:

=LOOKUP(200,--MID($A2,LEN($B2)+1,{1,2,3,4}))

in E2:

=REPLACE($A2,1,FIND($C2,$A2)+LEN($C2)-1,"")

[exceldoubts]

Age is showing like this Cell C2 = 4, and Cell D2 = 9 Director

[Phuocam]

Please edit the title like this:

Separate names, ages and positions into different columns.

See attached file.

[Phuocam]

Thanks for edit title.

Try this in C2:

=MAX(IFERROR(--MID($A2,LEN($B2)+1,{1,2,3,4}),0))

Array formula, enter with Ctrl+Shift+Enter.

[exceldoubts]

now name and age is coming fine in the sheet but the position is missing now.

[Bo_Ry]

Please try
B2
=LEFT(A2,FIND(C2,A2)-2)

C2
=--MID(A2,MIN(FIND({0,1,2,3,4}+{0;5},A2&1/17)),2)

D2
=MID(A2,FIND(C2,A2)+3,20)

[exceldoubts]

I have tried with ctrl shift enter. it worked fine. Thanks a lot.