Home > OS >  How to separate different data types of values in a column in Excel
How to separate different data types of values in a column in Excel

Time:09-24

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.

CodePudding user response:

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.

enter image description here

  • Related