excel - How to separate different data types of values in a column in Excel

Question

I have an Excel problem, and I hope everyone can help me with it. Thank you.

So I have a column as below full of phone numbers and full names:

*People had option to put their full names or just their phone numbers when joining a Zoom meeting.

Full_Name	Department
14044445555	A
Bob Max	A
16788889999	B
Sarah Harrison	C

How can I separate them into a table as below:

First_Name	Last_Name	Phone_Number	Department
		14044445555	A
Bob	Max		A
		16788889999	B
Sarah	Harrison		C

I have tried to use "Text to Columns", but it only worked with people who put their full names.

Thank you very much.

标签： excelexcel-formulaexcel-2010

Answer 1

If you want to do it by formulas only then add three columns before department. As per my below screenshot use formulas like

B2=IF(ISNUMBER(A2),"",TRIM(LEFT(SUBSTITUTE(A2," ", REPT(" ",100)),100)))
C2=IF(ISNUMBER(A2),"",TRIM(RIGHT(SUBSTITUTE(A2," ", REPT(" ",100)),100)))
D2=IF(ISNUMBER(A2),A2,"")

Then drag down and across right as needed.